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

Question

I have Windows 10 pro x64, Excel 2016 32 bit and Sql server 2017. I want to import excel file to sql server, I need to use 32-bit wizard because Microsoft Excel is not shown in 64-bit version, but I face this error: The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data)

score 9 · Answer 1 · answered Oct 17 '18 at 18:31

9

If you are having problems installing the engine because components are already installed, do this (from Microsoft):

If Office 365 is already installed, side by side detection will prevent the installation from proceeding. Instead perform a /quiet install of these components from command line. To do so, download the AccessDatabaeEngine_x64.exe to your PC, open an administrative command prompt, and provide the installation path and switch Ex: C:\Files\AccessDatabaseEngine_x64.exe /quiet

answered Oct 17 '18 at 18:31

CesarL

91
1
1

Although my situation was reversed, this worked for me (I had the 64 bit version but SSMS import wizard looks for the 32 bit version). Before trying a side-by-side, I looked for the source. Here's what I found: https://support.microsoft.com/en-us/help/2874601/can-not-use-access-odbc-or-oledb-provider-outside-office-c2r-apps – puddleglum Apr 22 '19 at 22:05
This is the solution to this problem – DJ. Dec 12 '19 at 15:20

score 7 · Answer 2 · answered Nov 02 '18 at 12:22

7

I had success doing the following (I use Excel 2016 and SSMS 2017) From Excel File -> Export -> Change File Type -> Excel 97- 2003 (*.xls)

answered Nov 02 '18 at 12:22

Karen Young

71
1
1

I didn't re-save the file but selected the older excel version still seemed to work for me – russelrillema Nov 06 '19 at 13:39
Question was about import, but I was doing this on "Export". Changing to 97-2003 works for me. Good enough if all you need is something like a CSV – AlbatrossCafe Jan 21 '21 at 22:54

jpcarts23 · Answer 3 · 2020-03-19T08:01:59.503

If you have OS(64bit) and SSMS(64bit) and already install the AccessDatabaseEngine(64bit) and you still received an error, try this following solutions:

1: direct opening the sql server import and export wizard.

if you able to connect using direct sql server import and export wizard, then importing from SSMS is the issue, it's like activating 32bit if you import data from SSMS.

Instead of installing AccessDatabaseEngine(64bit) , try to use the AccessDatabaseEngine(32bit) , upon installation, windows will stop you for continuing the installation if you already have another app installed , if so , then use the following steps. This is from the MICROSOFT. The Quiet Installation.

If Office 365 is already installed, side by side detection will prevent the installation from proceeding. Instead perform a /quiet install of these components from command line. To do so, download the desired AccessDatabaseEngine.exe or AccessDatabaeEngine_x64.exe to your PC, open an administrative command prompt, and provide the installation path and switch Ex: C:\Files\AccessDatabaseEngine.exe /quiet

or check in the Addition Information content from the link below,

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

it only worked with /quiet for me, and I don't have office on the machine, strange. — Nagy Ladislau Radu, Sep 22 '20 at 08:33
@NagyLadislauRadu , have you tried to check it in your installed programs? — jpcarts23, Sep 22 '20 at 09:48
yes, but the issue for me that installing it normally didn't work, same error from SSMS. Then I uninstalled it, installed it with /quiet, and it works. — Nagy Ladislau Radu, Sep 23 '20 at 06:58

score 4 · Answer 4 · answered Mar 21 '18 at 16:19

4

Use this link to download the 64 bit version of the Microsoft Access Database Engine 2016 Redistributable:

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

Once installed you can open the import export wizard 64 bit and you will have a data source option for excel.

answered Mar 21 '18 at 16:19

Matt

500
7
18

How do you get to the 64-bit import wizard? I can't find it with SSMS 18.2. – bendodge Sep 24 '19 at 21:54
I don't think it comes with SSMS - you have to get it from the Access DB engine or at least I did. – Matt Sep 26 '19 at 15:12

score 1 · Answer 5 · answered Aug 08 '19 at 18:54

This is a workaround solution. Ultimately, converting the Excel document to a CSV and using the Tasks/Import Data/Flat File Source option imported my data (although, I was not able to successfully map my datatypes in the import, which I can fix with CAST() later). On upload, change File type to CSV from TXT.

I have Office 365. I used a CSV and gave up on XLSX because:

When I ran the 32 bit version of the AccessDatabaseEngine.exe, I received this error:

When I ran the 64 bit version of AccessDatabaseEnginex64.exe, I received this error:

score 0 · Answer 6 · answered Apr 06 '18 at 08:48

0

If you have the Microsoft Access Database Engine and still are facing the same issue make sure that you are accessing the Microsoft SQL Server Management Studio as Administrator.

answered Apr 06 '18 at 08:48

B. Irena

169
7

score 0 · Answer 7 · answered Apr 12 '20 at 08:01

Instead of using the import/export tasks provided under the database, I utilized "SQL Server 2016 Import and Export Data (64-bit)" service comes with MS SQL Server 2016 installation as per suggested here. In windows 10 you can find it under SQL Server 2016 app. In your case, find SQL Server 2016 Import and Export Data (32-bit) service available in same location.

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

7 Answers7