How to specify MS Access workgroup file in Sql Server linked server?

Question

I have an Sql Server database that includes a linked server to an MS Access mdb database. The Access database has workgroup security and requires a valid user name and password. This is easily set up in the linked server dialog in sql management studio. The problem is that the Sql Server needs to be able to find the MS Access mdw file to be able to login.

The only way I have managed to get this to work is by setting the Registry value SystemDB of the registry key :

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines

to the path of the workgroup file but this causes other problems (like with other MS Access apps or Excel).

Is there a better way of doing this?

Answer 1

I haven't done this for a while, nor in recent versions of SQL server, but I seem to remember that when you add a link to a Jet database it provides the opportunity to define the workgroup, username and password. Certainly if you're using ODBC, that's part of the basic ODBC driver for Jet.

--
David W. Fenton
David Fenton Associates