Creating a linked server to a MS Access database with a Workgroup file (.MDW)

Introduction
In a former post i've shown how to open a MS Access database with the OPENDATASOURCE command but that is not always possible when the installation of SQL Server has a tight policy. SQL Server recommends using a linked server. In this post i'll explain the usage of a Linked Server in SQL Server for opening a MS Access file secured with a workgroup file (.mdw).

The problem
If the installation of SQL Server has tight security policy you'll recieve an error when you run the following OPENDATASOURCE code:

SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="E:\tmp\test.mdb";Jet OLEDB:System Database="E:\tmp\Beveiliging.mdw";User ID=testuser;Password=test;')...test;

GO

------

Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

The solution
I've found some information about accessing a MS Access database with a workgroupfile (.mdw) on MSDN. It states the following : "To access a secured Access database, configure the registry (using the Registry Editor) to use the correct Workgroup Information file used by Access. Use the Registry Editor to add the full path name of the Workgroup Information file used by Access to this registry entry: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB"

Hmmm. I think that administrators are not happy with this kind of solutions and what to do in case you have two MS Access databases with a workgroup file?

Change that into

Execute the following code

USE [master]
GO


EXEC master.dbo.sp_dropserver @server=N'LSTest', @droplogins='droplogins'
GO


EXEC sp_addlinkedserver
@server ='LSTest',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'E:\tmp\test.mdb'
go


EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LSTest',@useself=N'FALSE',@locallogin=NULL,@rmtuser=Testuser,@rmtpassword=test


SELECT * FROM LSTEST...test
GO

Resulting in:

Conclusion
Well, I'm not really a fan of this solution. An administrator won't be happy when I suggest this solution. It seems to me that this works only for one MS Access database with a mdw file on a server. Not a practical solution when you have multiple MS Access databases with a workgroup file.


Greetz,
Hennie