I have a MS Access 2002 database which needs to run a SQL Server DTS
package from a VBA procedure.

When the MS Access DB is located on the SQLServer's server the DTS
runs fine.

However, when the Access DB is located on a different server the DTS
doesn't run and no error message is received - the VBA code just
finishes.

The code used to execute the DTS is:

.... Dim objPkg As DTS.Package

Set objPkg = New DTS.Package
objPkg.FailOnError = True

objPkg.LoadFromSQLServer "ServerName", "user", "pswd", , , , ,
"DTSName"
objPkg.Execute

Set objPkg = Nothing ...


The connection to SQL Server is fine as I can reference properties of
the package, but the .Execute seems to fail.

Does anyone have any ideas?

Thanks for any help in advance.

Chris.

Chris,

Keep in mind that a DTS package runs on the client, that is, on the machine
from which you call the package. Therefore (1) you need all the DTS DLLs and
support files on the client, as listed in REDIST.TXT, and (2) any pointers
in the package itself to external resources, especially file system paths,
must be valid from all machines where the package might be executed (which
normally means UNC pathnames).

-Doug


--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA

This posting is provided "AS IS" with no warranties, and confers no rights.

"Chris" <chrissmith_76 (AT) yahoo (DOT) co.uk> wrote

Doug,

Thanks very much for your input which was very helpful. The problem
was resolved by creating a ODBC connection from the end-user machines
to the SQLServer (on it's own machine). I already had the DTS DLLs.

Thanks again,
Chris

"Douglas Laudenschlager [MS]" <douglasl (AT) online (DOT) microsoft.com> wrote: