Access 2007 Database as Linked Server in SQL 2008 R2

I had some 32-bit Access 2007 Databases that I wanted to configure as Linked Servers on my 64-bit SQL 2008 R2 server. Seemed simple enough, however, I ran into a couple bumps along the way. For instance, every time I tried to add the database I got the following error:

The linked server has been created but failed a connection test. Do you want to keep the linked server?

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "[servername]".

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "[servername]" returned message "Could not find installable ISAM.". (Microsoft SQL Server, Error: 7303)

Quick Note: The error above was easily fixed by not entering anything into the Provider String field. If that doesn’t work follow along below to see how I was able to get it to work.

Step 1. Install the Access 2010 Database Engine (32-bit) This is a free download directly from Microsoft.

The Access 2010 Database Engine is required because Windows or SQL server do not include the engine part of their installs. I am using the 32-bit engine because my databases are 32-bit. Could be wrong, but, I don’t believe the 64-bit engine can read a 32-bit database… At least Access 2007 64-bit couldn’t read my 32-bit databases…

If you skip this Microsoft Office 12.0 Access Database Engine OLE DB Provider will not appear in the drop-down box when you go to create the Linked Server.

Step 2. Allow InProcess For The Microsoft.ACE.OLEDB.12.0 Provider After I was created a Linked Server (as we will do in the next step) I noticed that when attempting to expand the tables in the database the following error appeared. Step 2 fixes this.

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "[servername]" reported an error. The provider did not give any information about the error.
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "[servername]". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7399)

In Microsoft SQL Server Management Studio expand Server Objects / Linked Servers / Providers and Right-Click on Microsoft.ACE.OLEDB.12.0 and choose Properties.

Place a checkmark in the “Allow inprocess” checkbox and click Ok.

Step 3. Create the Linked Server Right-Click on “Linked Servers” Click “New Linked Server…”

Enter the following fields: Linked Server: MYLINKEDSERVER (You choose what you want it to be called) Server Type: Other data source Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider Product Name: Access Data Source: File Location (Enter the location of the Access database, UNCs are allowed just make sure share and file permissions are ok) Provider String: (empty)

That’s it! It should be working now.