Execute DTS package (1 Viewer)

[Dave_cha]

Hi folks,

I'm trying to execute a DTS package on a local MS SQL server with the click on a button on an MS Access form.

Does anyone have any code which would do the trick?

I managed to find the following but I'm getting a "User-Defined Type not defined" error on 'DTS.Package' on line 1.

Thanks, Dave

Public Sub ExecuteDTSPackage()

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 = "DCHAMBERLAIN"
sUsername = "ff13859"
sPassword = ""
sPackageName = "EIDB Feed Creation"


' Load Package
oPKG.LoadFromSQLServer sServer, , , _
DTSSQLStgFlag_UseTrustedConnection, , , , 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