Unanswered: Trying to run DTS package from Microsoft Access
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"
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]?
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
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??