Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    57

    Unanswered: DTS Errors with Access?

    Hi,

    I have a DTS package that when executed through Enterprise Manager works just fine.
    I have an Access app on a different computer that executes the DTS package via VB using the syntax below. This process causes the DTS package to return an error of 'SQL Server Does Not Exist or Access Denied'.
    I have tried both authentication types, not making a difference. I have used the SA login from Access and still get the error. All the necessary DTS drivers are installed on the second box and I can connect to the SQL tables succesfully also...
    Any ideas? Thanks!

    Private Sub cmd_MPSDTS_Click()

    Dim oPackage As New DTS.Package
    On Error GoTo eh

    oPackage.LoadFromSQLServer "SVRName", "sa", "pwd", DTSSQLStgFlag_Default, "", "", "", "Pkg_Name", 0
    'Execute the Package

    oPackage.Execute
    'MsgBox oPackage.Description, vbInformation, _
    "Re-import Excel sheet."

    'Clean up.
    MsgBox ("Ran DTS Package")
    Set oPackage = Nothing
    Exit Sub
    eh:
    MsgBox Err.Description, vbCritical, _
    "Error Running Package"

    End Sub

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    I assume you have tried pinging the server from the machine with the access db? or rather from the machine that opens the access db.

  3. #3
    Join Date
    Feb 2005
    Posts
    78
    You should also check that the user you are executing as from Access is the same as the user that is executing from Enterprise Manager or at least has the same privileges.

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Not sure why that would make a difference (Not saying it wouldn't, I'm just not sure why it would). The error they are receiving makes it look as though they can't reach the server at all....

  5. #5
    Join Date
    Oct 2003
    Posts
    57
    Found the problem,

    The DTS package imported information from an Informix database. The necessary drivers weren't installed on the second machine so the job failed even though I could connect to the SQL server just fine. I got around it by changing the code to execute a SQL Job which in turn executed the DTS package. This way it ran on the SQL server.

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •