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:
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!
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!
(Lifetime Access/ Live Demos / Downloadable Resources and more!)
Artemakis Artemiou is a seasoned Senior Database and AI/Automation Architect with over 20 years of expertise in the IT industry. As a Certified Database, Cloud, and AI professional, he has been recognized as a thought leader, earning the prestigious Microsoft Data Platform MVP title for nine consecutive years (2009-2018). Driven by a passion for simplifying complex topics, Artemakis shares his expertise through articles, online courses, and speaking engagements. He empowers professionals around the globe to excel in Databases, Cloud, AI, Automation, and Software Development. Committed to innovation and education, Artemakis strives to make technology accessible and impactful for everyone.
Views:12,400
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept All”, you consent to the use of ALL the cookies. However, you may visit "Cookie Settings" to provide a controlled consent. Read More
This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
Cookie
Duration
Description
cookielawinfo-checkbox-analytics
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional
11 months
The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy
11 months
The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.