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.