Access -> Sql Server ActiveX Script in DTS Fails

[markgrizzle]

Hi All:
I need to append access data to my sql server table each night. Every month, the data is located in a new network folder, so my DTS package uses an ActiveX script to get the data path from a sql server table.

When I test it, it fails on this line:
cnn.Open= "Provider= Microsoft.Jet.4.0;DataSource=" & CurrentPrintRunDataSource & ";UserID = 'UserName';Password = 'password';"

This is the dialog displayed:

Package Error
Error Source: Provider

Error Description: Error Code: 0
Error Source= Provider
Error Description: Authentication failed.

Error on line 34

Authentication failed.

I've tried it without any userid or password (sine the mdb isn't protected. I've also tried it with my personal settings but got the same response.

The network admin says I can't use the server admin password for this.

As always, any help is appreciated.
Mark

 

[markgrizzle]

Turns out I'd renamed a variable... sorry about that