Add an access database as a LinkedServer in Microsoft SQL 2008 R2
USE [master] GO EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1 GO EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1 GO EXEC sp_addlinkedserver @server = N'LinkedServer', @provider = N'Microsoft.ACE.OLEDB.12.0', @srvproduct = N'OLE DB Provider for ACE', @datasrc = N'C:\location\accessdb.mdb'; GO Select * from openquery(LinkedServer,'Select * from tablename') Select * from LinkedServer...TableName
If you get the following error in the server:
The OLE DB provider “Microsoft.ACE.OLEDB.12.0” has not been registered.
Go to:
http://www.microsoft.com/en-us/download/details.aspx?id=13255
Get the Version you need (64 Bit, 32 Bit)