If you need to create a linked server from SQL 2014 to an old access database it is not as straightforward as it might sound.
My own experience of this turned into a trip down a few rabbit holes.
(This can also apply if you are trying to import data, or some other method of connecting to an access database, or excel from SQL Server)
The first message you will get will say something like
“The OLE DB provider “Microsoft.ACE.OLEDB.12.0″ has not been registered.”
The solution potentially has a few parts to it. In this article I will step through them.
- On your SQL Server you will need to install the MS Access Database Engine 2010 Redistributable 32 bit or 64 bit version.
Download and install from Microsoft.
- Run the below
USE [master] GO EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 ----------------------------------------------------
If you are still getting messages about it not being registered contine:
After you have done this, hopefully it will work. If so good luck to you.
Or…if your experience is anything like mine you will get a message along the lines of..
“OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server returned message “Unspecified error”.”
“Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0”
This was incredibly frustrating for me. I figured that as I was dealing with access 2007 perhaps an earlier version of the access database engine would work?
I downloaded the version for Office 2007.
The problem now was that this version only comes in 32 bit.
I was running out of ideas so in a test setup I installed the 32 bit version of SQL Server 2014 express and tried with that.
Sadly I ended up with the same error.
Using PROCMON to troubleshoot
The next step for me was running procmon.
I filtered the processes to SSMS.exe and sqlsrvr.exe. This is where I noticed something amiss.
There was an access denied message to this folder. C:\Users\MSSQLSERVER\AppData\Local\Temp.
It was running as the NT Service\MSSQLSERVER username.
When I check the folder permissions that user has full control.. Huh?
I tried turning off UAC. Still no luck but fortunately another little clue.
Now I got this message.
“OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “xxxx” returned message “Cannot start your application. The workgroup information file is missing or opened exclusively by another user.”.
Funnily enough the way I eventually got this to work was by removing the password that I was given! Thanks to this thread.
Just to clarify I was given a username admin and a password for the access database. I removed the password, set it to nothing. Then it worked.