tech question: how do you call a DTS package from Access?

[sui generis]

I can't successfully hook the dang thing up to a form - have tried embedding the VBA code in the module and still getting errors.

Anybody have experience with this?

Should I use the dtsrun command instead? What if the end user doesn't have SQL Enterprise Manager installed, will that still work?

[LynzM]

1. dunno, but...

I'd suggest searching experts-exchange.com. You can google to individual links, if you don't want to sign up. I got all my good Access answers there, when I was learning. Unfortunately, I have no experience with what you're asking about :(

[Imperialism Inc.]

2. I'm pretty sure the end user needs SQL server client tools installed

DTS might come with Visual Basic 6 as well. Not sure if it is redistributable though.

As far as how to do it you might try using the below wizard to get some baseline code to work with. I think the method you want is the Execute method. DTSrun is a command line utility.

http://support.microsoft.com/kb/239454/EN-US/

[UncleSepp]

3. What version of SQL Server?

Also, in what form is the DTS package? PM me if you don't want to post all the gory details to the world :-)

[sui generis]

4. it's SQL Server 2000 - DTS has been dumped to code

DTSrun works fine but when I paste the .bas code into the module as a call for the form button I get errors.

I just found this article:

http://www.winnetmag.com/SQLServer/Article/ArticleID/7444/7444.html

on my way to read it now

[Imperialism Inc.]

5. What specific errors are you getting?

[Imperialism Inc.]

6. Hmm could it be this....

I don't remember having to do this but then I've only used DTS programatically once and that was 4 years ago.
http://www.sqldts.com/default.aspx?208,1

Before going any further the one drawback to using Visual Basic is that it is apartment threaded and DTS is free threaded. This can cause exception access violations. The simple way top overcome this is to ensure all steps execute on the main package thread. To do this set the ExecuteInMainThread property as illustrated below:

    ' Set Exec on Main Thread
    For Each oStep In oPKG.Steps
        oStep.ExecuteInMainThread = True
    Next

==
Some sample code:
http://www.sqldts.com/default.aspx?208,2

Private Sub ExecutePackage()

    Dim oPKG As DTS.Package, oStep As DTS.Step
    Set oPKG = New DTS.Package
    
    Dim sServer As String, sUsername As String, sPassword As String
    Dim sPackageName As String, sMessage As String
    Dim lErr As Long, sSource As String, sDesc As String
    
    ' Set Parameter Values
    sServer = "MyServer"
    sUsername = "MyUser"
    sPassword = "MyPassword"
    sPackageName = "MyPackage"
    
    ' Load Package
    oPKG.LoadFromSQLServer sServer, sUsername, sPassword, _
        DTSSQLStgFlag_Default, , , , sPackageName
        
    ' Set Exec on Main Thread
    For Each oStep In oPKG.Steps
        oStep.ExecuteInMainThread = True
    Next
    
    ' Execute
    oPKG.Execute
    
    ' Get Status and Error Message
    For Each oStep In oPKG.Steps
        If oStep.ExecutionResult = DTSStepExecResult_Failure Then
            oStep.GetExecutionErrorInfo lErr, sSource, sDesc
            sMessage = sMessage & "Step """ & oStep.Name & _
                """ Failed" & vbCrLf & _
                vbTab & "Error: " & lErr & vbCrLf & _
                vbTab & "Source: " & sSource & vbCrLf & _
                vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
        Else
            sMessage = sMessage & "Step """ & oStep.Name & _
                """ Succeeded" & vbCrLf & vbCrLf
        End If
    Next
    
    oPKG.UnInitialize
    
    Set oStep = Nothing
    Set oPKG = Nothing
    
    ' Display Results
    MsgBox sMessage
    
End Sub

[hunter]

7. Shouldn't this go in the Computer Help and Support group?

http://www.democraticunderground.com/discuss/duboard.php?az=show_topics&forum=242

Besides, I get sick whenever I think about VBA.

[sui generis]

8. prolly - I thought it got rolled into this forum for some reason

my bad. Sorry about your illness. I say we propose a constitutional amendment to keep people who use VBA from getting married.