DTS and Microsoft Access

[drtree]

Hi all,

I have an Access database that is password protected that I need to update every night. I want to use DTS to do this, but was wondering how I could set it up so that I only update certain rows, how do I go about this? It looks like, at least on the surface, that the only thing I could do would be to bring the data into SQL Server, change it and then export it back to Access.

Thanks,

Doc Tree

 

[gradley]

Have you tried setting up the Access database as a linked server?

You should be able to perform update statements against the linked database.

 

[drtree]

Gradley, thanks for the reply and yes I have. I attempted to set up the Access db as a linked server, but have been having a lot of trouble getting it set up as it is password protected (When the Access db is opened it prompts you for a password - no user name is entered). I have tried to set up it so that every login mimics the Admin account (with the proper password) but am not able to login - that's why I was going to try using DTS.

Doc Tree

 

[gradley]

On the Security Tab of linked server properties, have you tried radio button "Be made using this security context" and specifying the user and password?

This has allowed me to connect to a secure Access database in the past.

 

[drtree]

Gradley, I've tried that to no avail. The file is located on the same server as the SQL Server, and when I try to set it up so that it attempts to log on using a specified security context it fails.

Thanks,

Doc Tree

 

[gradley]

Ok - here is a different approach.

Because it is on the same server, try setting up a Microsoft Universal Data Link file for the Access Connection.

In DTS, create a UDL connection for this database. Add an Execute SQL task and try performing an update.

If you've never created a UDL file before, do the following:

1. Create a new text file and save it with a .UDL connection.
2. Double click on the file and enter the necessary connection parameters for the Access database.
3. Test the connection and then save the file.