How to Fix The “Microsoft.ACE.OLEDB.16.0 Provider is not registered on the local machine.”

A popular issue in importing the data which is in Microsoft Excel 2016 to MSSQL via SSMS (Management Studio). It throw an error :

“===================================

The ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered on the local machine. (System.Data)


To fix this,

First thing first, install “Microsoft Access Database Engine 2016 Redistributable.”

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

And then also check the version of Excel and SSMS. They need to be same, like 32 vs 32 or 64 vs 64

SSMS is a 32 bit client, if your Microsoft Excel is 64 bit. you may need to install the 64 bit version of the Microsoft Access Database Engine 2016 Redistributable and try opening – the 64-bit  “SQL Server Import and Export Data” directly. Go to Start Menu -> Microsoft SQL Server 2017( your SQL server version) ->  SQL Server  Import and Export Data (64-bit)> Open the application and try to import data using the “Excel 2016” option, it should work fine.

Another solutions:

Tips 1, use csv instead of xls

What I did is I converted the excel file into a csv file (comma delimited) , then I used the option Data Source “Flat File Source” and it recognized the comma separated fields as columns and created a Table. I also uninstalled the redistributable to keep my machine clean. 

Tips 2, use /quite option to install 32bit ACE

I downloaded Microsoft Access Database Engine 2016 Redistributable.  I reinstalled 64-bit version.  I had no problem since I already had Office 64-bit installed.  But, SSMS Import did not work probably for being 32-bit app.

I downloaded 32-bit distributable separately.  I tried to run it, but it complained that I already have 64-bit installed.  I ran it from the command prompt in /quiet mode.  It installed.  SSMS import worked on Excel 2016 file now.  I did not need even to restart SSMS.