“Microsoft.ACE.OLEDB.12.0” cannot be loaded in-pro

Question

I can not find a solution for this error:

The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.

I found a lot of posts on the web but none work.

I am working with SQL server 2014 64 bits and office 2013

Code that generates the error:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                         'Excel 12.0 Xml;HDR=YES;Database=C:\SSIS\Table_nm.xlsx', 
                         'SELECT * FROM [Table_nm$]'); 

Answer 1

As it turns out, you can get the 64-bit driver from here.

https://www.microsoft.com/en-us/download/details.aspx?id=13255

After you download it, don't double-click it to install it, install it using the command prompt, which should be something like this:

"C:\Users\rshuell001\Downloads\AccessDatabaseEngine_x64.exe" /passive

The '/passive' is the key here because it prevents the installation from failing if you already have the 32-bit version installed (via 32-bit Office).