Problems running DTS package remotely

[jtrembla]

I can successfully run the DTS package from SQL SERVER, but my vba script (from MS Access) fails. It runs the package, but it fails at every step. I even tried supplying the sa username and password. It seems to be a permissions issue somewhere, I just can't find it.

I gave myself owner permissions of the database and system admin.

Here is my code

Function RunPackage()

Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1

Dim oPkg As Package
Dim oStep, sMessage, bStatus
    
Set oPkg = New DTS.Package
oPkg.LoadFromSQLServer "Myserver", "sa", "Mypassword", DTSSQLStgFlag_Default, "PackagePassword", "", "", "PackageName"
oPkg.Execute
    
    bStatus = True
    
    For Each oStep In oPkg.Steps
        sMessage = sMessage & " Step [" & oStep.Name & "] "
        If oStep.ExecutionResult = DTSStepExecResult_Failure Then
            sMessage = sMessage & " failed"
            bStatus = False
        Else
            sMessage = sMessage & " succeeded"
        End If
        sMessage = sMessage & "Task " & oPkg.Tasks(oStep.TaskName).Description
    Next
    
    If bStatus Then
        sMessage = sMessage & "Package [" & oPkg.Name & "] succeeded"
    Else
        sMessage = sMessage & "Package [" & oPkg.Name & "] failed"
    End If
    
    MsgBox sMessage
    MsgBox "Done"
    
End Function

Any Help would be great, this is annoying me.

 

[unclerico]

enable logging for the package, run your task again from your Access app, once it is finished, go back and look at the log for the package it will tell you why it failed.