Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

Transact-SQL
1

Hello,
I am trying to run the following in SQL Server 2008 - management studio: and I am getting the error as below:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Extended Properties=Excel 8.0;HDR=Yes;IMEX=1;Database=C:MASTER.xlsx',
'SELECT * FROM [Variable Spreadsheet- Invoicing$]')
Error:
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I have the Access database engine 2010 installed on the server.
How do i fix this?

Thank you.

2

I need to add the following to my issue:
when I go to ODBC data sources on the server, i do not see Excel driver in Drivers tab, but when I go to ControlPanel\programs and features, Microsoft Access database engine 2010 is there. I tried to import data manually using the Import wizard from the same spreadsheet and it is working without any issues.

Thanks.

3

Could it be, that you are missing the full path?

4

The full path is there in my code, I just took it away from the code here. Also, when I use C:\Windows\SysWOW64\odbcad32.exe, I do see the Excel driver for .xlsx with version 14.

Thanks.

5

Sounds like you have the x86 version of Access installed - and an x64 version of SQL Server. x64 SQL Server cannot access the x86 drivers.

6

Thank you Jeff.