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

    Unanswered: Trying to run DTS package from Microsoft Access

    Hello,

    I am trying to execute a DTS package that updates a SQL database from a Microsoft Access form using a command button. The computer I created the access app on is the same computer that hosts the SQL Server and when I run it form there it works fine. However when I run it from another machine across the network the package does not work and I am having login failure problems with other users even though I have added them to the database. I need to use Windows Authentication for this, here is the code I am using in VB on the MSFT Access form. Do I need to specify username & password if specifying a trusted connection?

    Private Sub Command0_Click()

    Dim dtsp As New DTS.Package
    dtsp.LoadFromSQLServer "MYSERVER", "SERVER_USER_NAME", _
    "USER_NAME_PWD",DTSSQLStgFlag_UseTrustedConnection , "", _
    "", "", "TGImport"
    dtsp.Execute

    End Sub

    Thanks Much!!

  2. #2
    Join Date
    Jan 2004
    Posts
    49

  3. #3
    Join Date
    Apr 2004
    Location
    The Netherlands
    Posts
    29

    Trying to run DTS package from Microsoft Access

    Do you have the DTS library object available on the other computer and also the microsoft SQL Server Client installed?

    Greetz DePrins

  4. #4
    Join Date
    Oct 2003
    Posts
    57
    The application seems to behave better now, no login errors. The DTS object library box is checked in the access app when I view the available libraries through the VB editor form the other computer(s). However now I get an error saying that the SQL table I am writing to is an invalid object name but the error only occurrs from the remote computers. ie: Invalid Object Name: 'database.dbo.tablename' this syntax seems wrong to me shouldn't it display as [database].[dbo].[tablename]?


    Thanks much!

  5. #5
    Join Date
    Oct 2003
    Posts
    57

    To be more complete...

    My DTS package was created using the designer, specifying and OLE DB source and and OLE DB destination of the SQL Server. The process takes the data from the source foxpro tables and copies it directly into SQL tables with identical structures, column names, etc. The VB code executes the package fine when working on the local machine with the server but remotely I now get the error:

    Step "DTSStep_DTSDataPumpTask_1" Failed
    Error:-2147217865
    Source: Microsoft OLE DB Provider for SQL Server
    Description: Invalid object name 'database.dbo.tablename'

    The table exists and the package works fine from the local machine, does this ring a bell??

    Thanks!

  6. #6
    Join Date
    Apr 2004
    Location
    The Netherlands
    Posts
    29

    this ring a bell

    This ring a bell but i have to dive deeper into it and search for the solution. If I remeber correctly I had the same problem an year ago. I will report back to you.

    Greetz,
    DePrins1968

Posting Permissions

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