The Microsoft OLE DB Provider for Jet provides an OLE DB interface to Microsoft® Access databases. Using the Microsoft OLE DB Provider for Jet, Microsoft SQL Server™ distributed queries can query Access data.
To set up a linked server to access an Access database:
For example, to create a linked server named Nwind that operates against the Access database named Nwind.mdb in the C:\Mydata directory, execute:
sp_addlinkedserver 'Nwind', 'Access 97', 'Microsoft.Jet.OLEDB.4.0',
'c:\mydata\Nwind.mdb'
This example enables access for the local user Joe to the linked server named Nwind.
sp_addlinkedsrvlogin 'Nwind', false, 'Joe', 'Admin', NULL
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 pathname of the Workgroup Information file used by Access to this registry entry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB
After the registry entry is configured, use sp_addlinkedsrvlogin to create login mappings from local logins to Access logins:
sp_addlinkedsrvlogin 'Nwind', false, 'Joe',
'AccessUser', 'AccessPwd'
Access databases do not have catalog and schema names. Therefore, tables in an Access-based linked server can be referenced in distributed queries using a four-part name of the form linked_server...table_name.
This example retrieves all rows from the Employees table in the linked server named Nwind.
SELECT *
FROM Nwind...Employees
The Microsoft OLE DB Provider for Jet 4.0 can also be used to access Microsoft Excel spreadsheets. This example shows creating a linked server that accesses an Excel spreadsheet:
sp_addlinkedserver N'Excel', N'Jet 4.0',
N'Microsoft.Jet.OLEDB.4.0',
N'c:\data\MySheet.xls', NULL, N'Excel 5.0'
GO
sp_addlinkedsrvlogin N'Excel', false, sa, N'ADMIN', NULL
GO