{"id":3325,"date":"2021-05-09T10:08:09","date_gmt":"2021-05-09T17:08:09","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3325"},"modified":"2021-05-09T10:09:19","modified_gmt":"2021-05-09T17:09:19","slug":"how-to-fix-the-microsoft-ace-oledb-16-0-provider-is-not-registered-on-the-local-machine","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3325","title":{"rendered":"How to Fix The “Microsoft.ACE.OLEDB.16.0 Provider is not registered on the local machine.”"},"content":{"rendered":"\n

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

\"\"<\/figure>\n\n\n\n

“===================================<\/p>\n\n\n\n

The ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered on the local machine. (System.Data)<\/p>\n\n\n\n

\n\n\n\n

To fix this,<\/p>\n\n\n\n

First thing first, install “Microsoft Access Database Engine 2016 Redistributable.”<\/p>\n\n\n\n

https:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=54920<\/a><\/p>\n\n\n\n

And then also check the version of Excel and SSMS. They need to be same, like 32 vs 32 or 64 vs 64<\/p>\n\n\n\n

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<\/a> 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.<\/p>\n\n\n\n

Another solutions:<\/strong><\/p>\n\n\n\n

Tips 1, use csv instead of xls<\/strong><\/p>\n\n\n\n

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. <\/p>\n\n\n\n

Tips 2, use \/quite option to install 32bit ACE <\/strong><\/p>\n\n\n\n

I downloaded Microsoft Access Database Engine 2016 Redistributable<\/a>.  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.<\/p>\n\n\n\n

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.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18,625],"tags":[724,722,723],"class_list":["post-3325","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","category-sql","tag-microsoft-ace-oledb","tag-microsoft-ace-oledb-16-0","tag-microsoft-ace-oledb-16-0-provider-is-not-registered"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3325","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3325"}],"version-history":[{"count":4,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3325\/revisions"}],"predecessor-version":[{"id":3330,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3325\/revisions\/3330"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3325"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3325"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3325"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}