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)

 

 

Related Posts

No Comments Yet.

Leave a reply

You must be logged in to post a comment.