How to add an Access Database as a linked server in SQL Server

Associated Error messages:
Error 7399

OLE DB provider '%ls' reported an error. %ls
Cannot start your application. The workgroup information file is missing or opened exclusively by another user.
http://msdn.microsoft.com/en-us/library/aa238103(SQL.80).aspx

Step 1) Register your MDW or MDA location for your MDB file


If your Access database is secured, and uses an MDW or MDA workgroup/security file to store user access, you will need to modify the registry on the SQL server to find the corresponding MDW or MDA file required by the MDB you are trying to link.


Complete documentation can be found here:
http://support.microsoft.com/kb/246255




Step 2) Create the Linked Server


Here is an example of the script you will need to run to create the linked server.

Complete documentation on the sp_linkedserver command can be found here

http://msdn.microsoft.com/en-us/library/ms175866.aspx

This script can be found by logging into SQL Management Studio, and going to 

View>Template Explorer
In the template explorer window, expand SQL Server Templates>Linked Server
Double-Click on Add Linked Server access MDB



-- ========================================
-- Add Linked Server Access MDB template
-- ========================================


EXEC sp_addlinkedserver 
@server = N'AccessTest', 
@provider = N'Microsoft.Jet.OLEDB.4.0', 
@srvproduct = N'OLE DB Provider for Jet',
@datasrc = N'C:\dbs\test\dailylog.mdb'
GO


-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin 
@rmtsrvname = N'AccessTest',
@useself = N'TRUE',
@locallogin = NULL, 
@rmtuser = 'Admin', 
@rmtpassword = null
GO


Once the above script has been run, you should be able to run each of the following to confirm if your link works correctly.


-- List the tables on the linked server
EXEC sp_tables_ex N'AccessTest'
GO


-- Select all the rows from the Customer table in the linked server
SELECT * FROM AccessTest...customer