Creating a linked server from SQL Server x64 to an Access database

In English so it might help someone else, but mostly for myself to remember how to set things up 8-)

Accessing a Microsoft Access database (.mdb) from SQL Server Management Studio by adding it as a linked server. In my case SQL Server 2014 x64 Developer Edition. Seems like an easy task but turns out to be quite complicated.

If you have a relatively new computer you are likely running 64-bit Windows and therefore also likely the 64-bit edition of SQL Server. With Microsoft Office however, the choice is not as simple. Sure there is both 32-bit and 64-bit versions of Office, but even Microsoft themselves says that you are better off installing 32-bit Office unless you work on very large documents or Excel spreadsheets. Main reason is that add-ons are rarely 64-bit and therefore unavailable in 64-bit Office.

So we have 64-bit SQL Server and 32-bit Office.

When trying to add a linked server in SQL Server we need some drivers to connect to Access and these need to be 64-bit drivers since SQL Server is 64-bit. OK, so we download the 64-bit Access runtime and run the installer which immediately pops up with the following error message.

So it seems that it's not allowed to install 64-bit drivers when I am using 32-bit Office. I don't understand why this is, but there you go. So my only option is to uninstall my entire office package and reinstall the 64-bit variant? I don't think so...
It turns out there's a semi-dirty solution to this problem. Just fire up a command prompt and run

AccessDatabaseEngine_x64.exe /passive

This will run the update without any interaction from the user and the drivers will be successfully installed. In SSMS the ACE OLEDB provider should now be available

Right-click on the Linked Servers Node and select New Linked Server...

You can also create the linked server using sp_addlinkedserver like:

EXEC sp_addlinkedserver 
   @server = N'MYDATABASE', 
   @srvproduct = N'Dontreally need a product name',
   @provider = N'Microsoft.ACE.OLEDB.12.0', 
   @datasrc = N'C:\tmp\MyDatabase.mdb';
GO

You can now write queries etc. ising the linked server, e.g.

SELECT * FROM MYDATABASE...MyTable

Creating a linked server is convenient but it's also possible to query a remote database using OPENROWSET or OPENDATASOURCE which does not require that you create a linked server.

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','C:\tmp\MyDatabase.mdb';'admin';'',MyTable)

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=C:\tmp\MyDatabase.mdb')...MyTable;


If you're getting access denied messages etc. Make sure the SQL Server account have read permissions on the folder where the Access database is stored. Also try running Management Studio as an administrator.

If you're thinking of installing the Access Database Engine for Office 2013, think again. There is no such thing. The closest thing I have found which probably is the equivalent is Microsoft Access 2013 Runtime. However with this installer the workaround with using /passive does not work but gives you the the same error message as when running without the /passive argument.

The Access 2010 runtime will work OK with Office 2013 though so this does not seem to be required.