The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered" - How to Resolve it - Article on SQLNetHub

The OLE DB provider “Microsoft.ACE.OLEDB.12.0” has not been registered – How to Resolve it

by

In this article, we will be discussing about the error message [The OLE DB provider “Microsoft.ACE.OLEDB.12.0” has not been registered] and how you can easily resolve it.

 

Importing Data from Excel to SQL Server via SSMS

In most of the cases, you might get the [OLE DB Provider “Microsoft.ACE.OLEDB.12.0” has not been registered] error message, when trying to import data from Excel to SQL Server (i.e. via SQL Server Management Studio, also known as SSMS) and especially when trying to import data from an xlsx Excel file, that is a more modern Excel file format (Excel 2007 and later) than the old xls file format (Excel 2003 and earlier).

So, why does this happen?

The reason you get the above error message, is because you need to install the necessary drivers, in order for SSMS to be able to properly read the Excel file.

 

Installing the Proper Microsoft Drivers

So, indeed, as the above error message suggests, you are missing certain drivers in order to be able to read from Excel files (version 2007 or later – xlsx file format).

To this end, the driver you are missing, is actually the Microsoft Access Database Engine 2016 Redistributable. and you can download it from here.

Now, when you visit the above download link and right after you click on the “Download” button, you are presented with 2 options. So, you can either download and install the 32-bit version of  Microsoft Access Database Engine 2016 Redistributable (file name “accessdatabaseengine.exe”), or the 64-bit version (file name “accessdatabaseengine_X64.exe”).

 

Scenario 1: On a PC with SSMS Installed but without SQL Server Installed

In case you are using SSMS on your PC without a SQL Server Database Engine installation on it, then you will need to install and use the 32-bit version of the Microsoft Access Database Engine 2016 Redistributable, that is the file with name “accessdatabaseengine.exe”, since the SSMS process that reads from Excel files, is a 32-bit process.

 

Scenario 2: On a PC with both SSMS and SQL Server Installed

In a different case, that is the scenario where your PC has SQL Server Database Engine installed on it along with SSMS, then you will need to install and use the 64-bit version of the Microsoft Access Database Engine 2016 Redistributable.

 

Handling Potential Issues when Installing the Access Database Engine 2016 Redistributable

So, in the scenario where you indeed need to install the 32-bit version of the Microsoft Access Database Engine 2016 Redistributable (that is Scenario 1), under certain circumstances, you might get an error message, saying that the 32-bit version of the Access redistributable cannot be installed, because you have other 64-bit Office products installed on the PC, for example: “Office 16 Click-to-Run Extensibility Component 64-bit Registration“.

Here are the relevant error messages:

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered" - How to Resolve it - Article on SQLNetHub

 

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered" - How to Resolve it - Article on SQLNetHub

 

In case you got the above error messages and you cannot proceed with the installation, here’s what you can try:

  • Start a command prompt as an administrator
  • In the command prompt, navigate to the folder where you saved the 32-bit version of the Microsoft Access Database Engine 2016 Redistributable file (file name “accessdatabaseengine.exe“) and run the installation in quiet mode as per the below command:
    • accessdatabaseengine.exe /quiet
  • Then, after the installation completes, try again to import data from the Excel file (xlsx) to SQL Server using SSMS

 

Hopefully, all the above solve the issue!

 

Learn essential SQL Server development tips – Enroll to our Online Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers”
(special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

Featured Online Courses:

 

Read Also:

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHubTV)!

Like our Facebook Page!

Check our SQL Server Administration articles.

Check out our latest software releases!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (5 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 12,400