SQL Server Microsoft.ACE.OLEDB.15.0

Question

I am writing a query to access what is in Excel sheet (2013 - 32 bit) and display that as a table in SQL Server 2014 SP2 64 bit. And I get this error:

The OLE DB provider "Microsoft.ACE.OLEDB.15.0" has not been registered.

I have tried installing Access Runtime 2013 and 2010, but yet the same problem persists. The query am using is:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.15.0', 
                         'Excel 15.0;HDR=YES;Database=C:\SheetName.xlsx', 
                         'SELECT * FROM [PayerList$]')

Preussen

You can't use a 32-bit driver from a 64-bit process. – Heterophyllous

@Panagiotis So, you mean I have to install 64 bit Office EXCEL ? – Preussen

Your SQL Server instance is running on the same machine as Excel? C:\SheetName.xlsx is that machine? – Sprint

No, I'm asking about the driver. You don't need Excel on the server at all. Your connection string asks for the driver installed with the Microsoft Access Runtime 2013. Your server is 64-bit so you should download the 64-bit version from here – Heterophyllous

@Sprint It's all local. But I would like to know how I can tackle the issue if they are not on the same machine. – Preussen

@AhaduMelesse querying Excel sheets like this is cumbersome and very slow. The preferred way is to import sheets into staging tables using SSIS, then query the staging tables. You can create any indexes you like in a staging table – Heterophyllous

@Panagiotis I have tried installing both 32 and 64, but I came to the understanding that Access Runtime needs to be the same bit as the server. – Preussen

@Sprint I will take that back, SQL Server is remote. – Preussen

@AhaduMelesse what do you mean? You have to install the 64-bit version of the 2013 runtime only. The 32-bit version is invisible to 64-bit applications like SQL Server – Heterophyllous

Answer 1

Microsoft.ACE.OLEDB.15.0 is the identifier of the OLEDB driver installed with the Access 2013 Runtime. It isn't installed with Excel. In fact, you don't even need Excel installed. You'll find the runtime here

Take care to download the correct runtime version. A 64-bit process can't use a 32-bit provider and vice versa. In your case, you should download the 64-bit version.

You should remove Excel from the server because it isn't used at all.

Heterophyllous

So, I install the Runtime on the server or on my local machine ? Whenever I try to install on my local, I get an error which says I have a 32 bit Office product already installed. I am thinking of two options 1 - Install the Runtime on the server if it helps 2 - Uninstall the 32 bit office product I have on my local and install a 64 bit one. Then try installing the 64 bit Runtime. – Preussen

I have the same issue. Office 2013 is 64bit, installing the 64bit runtime won't work in SQL Server import, so I tried the 32bit runtime, but it won't allow this. Catch-22! Why after 2 decades of SQL server, they don't have Excel importing built in? – Interrogatory

Answer 2

I was facing similar issue. I just changed program property from visual studio Compile > Target CPU from x86 to x64 (or Any CPU). This worked for me. My machine is 64 bit and program was written for x86.

Mellisamellisent

Answer 3

I just had the same issue. There is a way around, and it is by installing the ACE.OLDB in passive mode. However, if you do not delete/rename the file mentioned below, you will continuously have EVERY office application run the initial setup menu at as soon as you launch it, which quickly becomes a pain... speaking from first hand experience.

So, all is explained at this link here. Look for the following post: Edited by Applied Maths NV Wednesday, February 20, 2013 3:35 PM Proposed as answer by Dave Kolb Thursday, February 21, 2013 4:48 PM Wednesday, February 20, 2013 2:37 PM

However, instead of breaking your head, just uninstall your older version of Office and install a 64 bit one, which will allow you to peacefully install the 64-bit driver too.

Adjunction