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

Question

I get this error when I try to connect to any excel through MSSQL Server Data Import i.e. SSIS package The Microsoft.ACE.OLEDB.16.0 provider is not registered on the local machine. (System.Data)

It is not the same version I think some other patching is needed

I don't see why this question was voted to be closed as a duplicate... It's clearly a newer version in the error message and needs a newer set of components! This question and answers helped me for my `Microsoft.ACE.OLEDB.16.0` issues. The question being pointed to by the close request is for `Microsoft.ACE.OLEDB.12.0` and the answers address THAT version. This question should be opened back up. — Arvo Bowen, Jan 23 '20 at 13:32
@ArvoBowen No problem. I reopened it. But both questions have the same solution. — Hadi, Mar 06 '20 at 08:05
How so? They are different versions requiring different downloads. They are related but have different solutions. If I followed the answer from the other question it would not solve my issue. — Arvo Bowen, Mar 06 '20 at 13:24

score 191 · Answer 1 · answered Mar 02 '17 at 20:02

191

For anyone that is still stuck on this issue after trying the above. If you are right-clicking on the database and going to tasks->import, then here is the issue. Go to your start menu and under sql server, find the x64 bit import export wizard and try that. Worked like a charm for me, but it took me FAR too long to find it Microsoft!

answered Mar 02 '17 at 20:02

Paul Dove

1,919
1
7
3

3

To have Excel available from the 64-bit version of DTSWizard, install the 2016 Access Database Engine redistributable: https://www.microsoft.com/en-us/download/details.aspx?id=54920 Source: http://sqlblog.com/blogs/john_paul_cook/archive/2017/06/11/sql-server-2016-import-and-export-wizard-and-excel.aspx I tested this myself and this works. Note that SSDT is a 32-bit application so if you launch the import/export wizard from it, it's the 32-bit version of the wizard that launches. This is still the case even as of Visual Studio 2017 and SQL Server 2017. – otravers Oct 16 '17 at 12:49
2

Thanks for your answer, very useful. I have Office (2016) 64-bit and SQL 64-bit AND the corresponding connector Microsoft.ACE.OLEDB.16.0. However I was getting the message "'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine." This simply did not make sense. After opening directly the Import wizard 64-bit I did not have any problem to import excel files. My impression is that by default the tasks->import path goes to the 32-bit wizard and that messes up with the 64-bit items, not sure. Thanks Paul. – Coffee Oct 29 '17 at 00:35
@otravers, I downloaded and installed the 2016 Access Database Engine redistributable, but it didn't help. Then it hit me, that's for Access, not Excel. – Rod Nov 17 '17 at 21:24
3

@Rod, despite the name, I assure you this does solve this specific issue with Excel and DTSWizard. – otravers Nov 18 '17 at 21:49
@Coffee, yes, if you launch DTSWizard from SSMS, it's the 32-bit version that will open up. – otravers Nov 18 '17 at 21:50
4

I don't have a x64 version of the Import/Export wizard – PeterX Mar 13 '18 at 01:53
This worked for me. From SQL Management no can't go, from import wizard, works nicely – Zoran P. Apr 24 '18 at 12:51
Thank you. In my case, it is called SQL Server 2017 Import and Export Data(64-bit). Yes, it works like a charm. – Hong Apr 24 '19 at 23:59
@Paul Dove I cannot find the x64 bit import export wizard and/or the start menu..I am using SQL Sever Management Studio v17.9 and if I go to Tools I see option Import and Export Settings which is different – K Bh Jul 16 '19 at 09:55
Works like charm! Thank you for saving the day! – Mohammed A. Fadil Aug 20 '19 at 11:25
After hours of research i found only this solution as working one. Thanks. My issue was with Export – programer Anel Mar 01 '20 at 12:44
You sir deserve all of the upvotes. Thank you, mucho. – Chicken Sandwich No Pickles Oct 05 '20 at 19:58
Dude, you're a life saver. Thanks! – SAR Mar 17 '21 at 19:31
I found the following information important - Microsoft does not mark the 64 bit version with a name that is different from the 32 bit version. My files show the 64 bit version (AceOledb16) as 153 MB 16.0.5044.1000 and the 32 bit version as 137 MB 16.0.5044.1000. It's important to see which version you have installed. Microsoft, can you please fix this? – Jamie Dec 23 '21 at 17:26
The 64-bit import/export wizard doesn't get installed with SSMS. You need to install SQL Server locally to get that tool. – HotN Jan 08 '22 at 00:17
Very nice information, you saved me a couple of days. Thank you very much. – mostafakvd May 19 '22 at 08:19

score 134 · Answer 2 · edited Feb 14 '19 at 22:15

134

Note: I am running SQL 2016 Developer 64bit, Office 2016 64bit.

I had the same issue and solved it by downloading the following:

Download and install this: https://www.microsoft.com/en-us/download/details.aspx?id=54920
Whatever file you are trying to access/import, make sure you select it as a Office 2010 file (even though it might be a Office 2016 file).

It works.

Source

edited Feb 14 '19 at 22:15

e_i_pi

4,271
4
25
44

answered Nov 20 '16 at 14:57

FA911

1,349
1
6
2

4

Worked perfectly for me, thanks! Did load my office 2016 file as office 2007-2010 and it worked well. So much better than trying CSV/TSV. – tc_NYC Jun 06 '17 at 02:15
3

Thanks for mentioning that I *shouldn't* choose Office 2016. Not sure I would've tried one of the other ones. – birdus Apr 18 '18 at 21:06
The first link is broken – jcvegan Feb 14 '19 at 15:12
1

I just updated the link to the current URL for Microsoft Access Database Engine 2016 Redistributable – e_i_pi Feb 14 '19 at 22:16
On a WS 2016 64Bit I had to use the 32Bit version to get it working... – Lambda Oct 10 '19 at 14:30
4

Had to run the (32bit) installer from command line with /quiet flag. Then it worked. Otherwise it was complaining. – Syntax Error Jun 15 '20 at 12:16
1

A key element here is that you have to have *SQL Server 64-bit* installed on your own computer. Simply installing MSSMS (SQL Server Management Studio) won't do it, because MSSMS is *always* 32-bit, while your 64-bit Office will only let you install the 64-bit version of the Microsoft Access Database Engine 2016 Redistributable (which won't work with the 32-bit MSSMS). – Alpi Murányi May 14 '21 at 22:07

score 30 · Answer 3 · answered Nov 04 '17 at 13:21

30

As a quick workaround I just saved the workbook as an Excel 97-2003 .xls file. I was able to import with that format with no error.

answered Nov 04 '17 at 13:21

Russ

301
3
2

Didn't work for me. Got an, "Unexpected error from external database driver (1). (Microsoft JET Database Engine)" – Rod Nov 17 '17 at 21:26
2

It took a couple of tries but in the end I found this the quickest solution to my problem. – Lysoll Oct 25 '18 at 20:17
2

This only works if you have less than 64K rows to import. – Cougar9000 Apr 17 '20 at 13:41
Careful that your spreadsheet does not have more than 65,536 rows, otherwise you can lose data doing this conversion. – Fiach Reid May 15 '20 at 09:22

score 21 · Answer 4 · answered Mar 19 '20 at 08:04

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

Wow this was a frustrating one. Turns out the SSMS 18.5 installer is 64bit, but the application itself is 32 bit. I kept installing the 64bit AccessDatabaseEngine office components with the same error. Looked at task manager, saw SSMS is 32 bit... Then, I used your method: C:\Files\AccessDatabaseEngine.exe /quiet because I have 64bit Office 2016 installed. This worked!! It successfully installed the 32bit database engine alongside 64bit office. — rjkunde, May 22 '20 at 19:44

score 13 · Answer 5 · answered Mar 13 '18 at 02:44

13

An alternative that works for me is to simply convert to a CSV.

answered Mar 13 '18 at 02:44

PeterX

2,585
3
28
41

2

To great problems, great solutions, thank you @PeterX – ElMatador Sep 16 '19 at 20:58
1

Worked like a charm! I just needed to select flat file instead of ms excel 2016 when I was uploading the csv. – kiradotee May 14 '20 at 09:47
Great! When the 'better' way doesn't work, use the 'easier' way... :) – aderchox Jan 11 '22 at 18:47

score 5 · Answer 6 · answered Nov 02 '16 at 00:59

5

ACE.oledb.16.0 dosen't work in the 64-bit os

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

answered Nov 02 '16 at 00:59

Vishe

3,069
1
22
20

9

It did not work I tried still getting the same error – Suhail Abdul Rehman Chougule Nov 02 '16 at 11:14
1

it works for importing Microsoft Excel 2007-2010 on SQL Server Import and Export Wizard – Junior Mayhé Dec 03 '17 at 13:34

questionto42standswithUkraine · Answer 7 · 2021-09-19T12:52:35.493

Against other answers here, you can just install "ACEDB driver 64-bit" next to the other 64-bit Microsoft program.

In my case, I had "Microsoft 365" 64-bit (Microsoft Office 365) installed.

Because of the error

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

I tried installing Microsoft Access Database Engine 2016 Redistributable. But I got errors for 32-bit and 64-bit installers.

64-bit:

You cannot install the 64-bit version of Microsoft Access Database Engine 2016 because you currently have 64-bit Office products installed. If you want to install 32-bit Microsoft Access Database Engine 2016, you will first need to remove the 64-bit installation of Office products. After uninstalling the following product(s), rerun setup in order to install 32-bit version of Microsoft Access Database Engine 2016: Office 16 Click-to-Run Extensibility Component 64-bit Registration

or 32-bit:

You cannot install the 32-bit version of Microsoft Access Database Engine 2016 because you currently have 64-bit Office products installed. If you want to install 32-bit Microsoft Access Database Engine 2016, you will first need to remove the 64-bit installation of Office products. After uninstalling the following product(s), rerun setup in order to install 32-bit version of Microsoft Access Database Engine 2016: Office 16 Click-to-Run Extensibility Component 64-bit Registration

I could not uninstall single components:

The steps:

No need to uninstall and reinstall anything.
Press "Download" at Microsoft Access Database Engine 2016 Redistributable

Choose AccessDatabaseEngine_X64.exe. No need to install 32-bit AccessDatabaseEngine.exe, against other answers.

Run the executable from command prompt:

C:\Users\myuser\Downloads>AccessDatabaseEngine_X64.exe /quiet

Press OK for the administrator rights if asked for.

After this, my macro could fetch data from an Excel worksheet. I checked my local Microsoft Access of the Microsoft 365 package and it still worked.

Credits go to You cannot install the 32/64 bit version of Microsoft Access Database Engine because you currently have 64/32 bit Office and its source at How to install Microsoft.Ace.oledb 32 bit with Office 64-bit?.

Very good instructions! In my case 32 bit fixed the problem. — Basssprosse, Nov 22 '21 at 11:00
@Basssprosse That is helpful, it clearly depends on the system, yours probably 32 bit then? Or put it easier, if the 64 bit does not work, check 32 bit of course. — questionto42standswithUkraine, Nov 22 '21 at 12:20
Oh, yeah, curiously my office-version is 32bit. I thought it is 64bit. Thank you! — Basssprosse, Nov 22 '21 at 13:22
I tried just installing this with the exe but it didn't work. I finally went through the process of using command prompt and /quiet and it did work. Can anyone explain to me why this is necessary? — Deathstalker, Nov 29 '21 at 22:13

score 2 · Answer 8 · answered Jul 26 '21 at 20:48

If you have Office 365:

Download 32 Bit version here
Run Command Prompt as Administrator --> C:\Downloads\AccessDatabaseEngine.exe /quiet
Close SSMS & Reopen, Try Importing File again.

Doesnt work?

Download 64 bit version here
Run Command Prompt as Administrator --> C:\Downloads\AccessDatabaseEngine_X64.exe /quiet
Close SSMS & Reopen, Import File.

Note: Path to dowbloaded executable might be "C:\Downloads\User\USERNAME\Downloads\AccessDatabaseEngine.exe" for example

My MS office-version is 32bit, so this solution worked for me! — Basssprosse, Nov 22 '21 at 13:23

score 1 · Answer 9 · edited Nov 26 '17 at 20:46

1

You have to use now the new XLSX-Driver from Access-Redist (32/64-Bit). The current XLS-Driver are corrupted since last cumulative update.

edited Nov 26 '17 at 20:46

Hadi

34,224
11
61
121

answered Nov 26 '17 at 12:39

bfuerchau

11
1

score 0 · Answer 10 · answered Feb 18 '21 at 10:13

After having both installed the Microsoft Access Database Engine 2016 Redistributable and found the x64 bit import export wizard as instructed in the answers above, I had one last hoop to jump through. I got this error 'Executing the query "CREATE TABLE ...." failed with the following error: "Culture is not supported ... 3072 (0x0c00) is an invalid culture identifier."'

The fix was to go to Control Panel -> Clock and Region -> change my "English (Finland)" region to pure Finnish (Finland). Other more traditional cultures such as en-US will work too. I think I first saw this solution here: Fix culture is not supported: 3072 by changing Region Formats

score -1 · Answer 11 · answered Aug 02 '20 at 14:36

-1

Follow these steps:

Go [here][1], download Microsoft Access Database Engine 2016 Redistributable and install
Close SQL Server Management Studio
Go to Start Menu -> Microsoft SQL Server 2017 -> SQL Server 2017 Import and Export Data (64-bit)
Open the application and try to import data using the "Excel 2016" option, it should work fine.

answered Aug 02 '20 at 14:36

Francesco Mantovani

6,988
5
50
75

Tried... Step 1-4, it doesn't work for me. – Kevin Ng Dec 15 '20 at 03:48
this worked for me; thank you – xinthose Feb 14 '22 at 17:28

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

11 Answers11

Linked

Related