I recently got a new laptop and had to go through the ritual of reinstalling all my programs and drivers. I sometimes work with SSIS locally to import data from Excel and occasionally do demos with Power BI where I read from an Access database so I needed to install the ACE OLE DB provider. If you try to do either of those things without the ACE OLE DB provider you will get a message like the one below.
The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine.
I needed both the 32-bit and the 64-bit adapters to cover my various use cases. I started by installing the 64-bit Microsoft Access Database Engine 2010 Redistributable. If you have ever attempted this, you know there is a secret. You cannot install the 2010 Access database engine with a different processing architecture (bittedness) from your Office install (or install both versions of the 2010 Access database engine) without a workaround. If you try, you will get an error message.
There are two options for getting the other version installed.
Option A: Install the 2007 Office System Driver: Data Connectivity Components
The 2007 Access database engine can read the same format, but didn’t have a 64-bit install. It doesn’t have the check to see what other products are on your machine and can co-exist peacefully with the 64-bit 2010 Access engine.
Option B: Install from the Command Line and Use the Passive Switch
- Open the command prompt.
- Type the file path and file name for your Access Database Engine install file.
- Follow it with a space and then “/passive” and then hit Enter.
- Delete or rename the mso.dll registry value in the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Common\FilesPaths
Note: A helpful commenter has informed me that for Access 2016 the switch is /quiet rather than /passive.
I wrote up a document for this a while back for a client, but I can never remember where I put it. So I’m posting this on my blog to make this info easier to find for me as well as you.
Thanks a lot Meagan, I missed this method for years !
a trio of questions:
a. why 2010 and not 2013 or 2016?…just wondering
dos. where does one get the download of AccessDatabaseEngine_x86.exe (64 bit version)
III. did you have Office professional such that there was Access 32 bit also…and is this affected?
Hi, Jim. Thanks for your comment. The format didn’t change much from 2010 to 2016 so unless you are looking for a specific features the 2010 driver will often work for a 2016 file. You can substitute the ACE 14 driver and you have the same problem here. I just happened to be dealing with the 12 driver.
You can get the 64-bit Access engine at https://www.microsoft.com/en-us/download/details.aspx?id=54920. Just choose AccessDatabaseEngine_X64.exe when prompted.
I have 64-bit office, but occasionally need the 32-bit driver when working on SSMS/SSIS imports. I originally ran into this issue when many of my clients were running 32-bit Office and I had them install 64-bit Power BI, which then required the 64-bit ACE driver. If you have 32-bit Access, you will run into the issue I have documented.
Office 32 bit has long been the recommended install. So as we know the Power BI 64 bit won’t connect and this is the dilemma (although I noticed there is no problem connecting to excel, just Access tables…).
Once you’ve downloaded the 64 bit driver; it is unclear to me how that is implemented. I assume it is installed into the machine with Office 32. Next, for instance, in PBI (which often is a separate machine) when you select the Data Source you are going to use file explorer to find the Access file and I don’t see at what point one has the opportunity to select the newly installed 64 bit driver. Admittedly I am typing this without yet having tried it and so just recalling my past linking attempts to an Access table.
When using Power BI Desktop, you do not get to select the driver. Power BI searches for the appropriate driver that matches the processing architecture. So if you have 64-bit Power BI, it will look for 64-bit ACE driver in the expected install location. The driver does get installed when you install Access (or a version of Office that includes it), but it’s not installed by default with versions of Office that don’t include Access.
32-bit Office is the recommended install for most business users, but anyone working with Power Pivot in Excel pretty much needs 64-bit office. So I have 64-bit office for that reason.
I appreciate your patience with me. (I am displaying my lack of understanding of drivers.) So the real world is this: Access has long been installed, 32 bit, for many years. Along comes new-comer Power BI wanting to connect to tables (64 bit). Telling the user they must re-install Office 64 bit will be not generally welcomed. But if I can install a 64 bit driver and PBI will find/use that – it’s a great approach…and the users aren’t involved at all. Can it work this way?
I’ve got a test environment I’ll try out next……
So as we know the Power BI 64 bit won’t connect and this is the dilemma (although I noticed there is no problem connecting to excel, just Access tables…). […] la version 64 bits et 32 bits en même temps, vous pouvez suivre l’article de blog de Meagan Longoria pour avoir le […]
[…] la version 64 bits et 32 bits en même temps, vous pouvez suivre l’article de blog de Meagan Longoria pour avoir le […] 12.0 Provider for Both 64-bit and 32-bit Processing (@mmarie) […]
I get the same messages using the passive switch
You are getting an error message back in the command line? Or a dialog box is popping up?
The technic of the /passive switch will not work with the MS Access Engine 2016 at all, stick to MS Access Engine 2010
The /quiet switch works for 2016
Thanks for the tip, David!
Hello Meagan
I’m wondering if you could help me out, I have the same issue that you posted on this forum. I have 64 bit Windows , 32 bit MS office. I was told to the following:
-Uninstall the 32-bit Database Access Engine
-Uninstall the 64-bit Database Access Engine
– Uninstall your 32-bit Office
– Reboot the computer
– Reinstall the 32 bit office
– Reinstall the 32bit Database Access Engine.
– Reinstall the 64-bit Database Access Engine.
If this processsed is incorrect, please let me know the correct process. I had also tried the suggested on this page, but still no joy. Anything you can help on this matter, will be greatly appreciated.
I think you will still run into the issue I noted in this post when you get to installing 64-bit Access after installing the 32-bit Access. If you are installing newer versions of Access, try /quiet instead of /passive.
Wowwwww
Amazing !!
Thanks !!
Works using option /quiet
My Installation is Office 2016 32 Bits
Thank you very muchh!! It helped me a lot !!
Wowwww Thanks Meagan !!
Works using /quiet option
My installation is MS Office 2016 32bits
Thank you! Big help! I was able to install both the 32-bit and 64-bit driver in minutes with the /quiet flag on my PC, and SSIS works now.
works on 64 bit sys wow.
Great solution .working fine for me.my system is 64 bit with 32 bit office .I tried your option 2 and its working for me
When you have two versions of this driver installed (and using office 365 64 bit) you will need to be careful if you ever need to repair office because it will wipe all your office applications off because it detects the 32 bit driver!
The \quiet worked!! Thank you soooo much
yeeppieee…..thank u Meagan 2nd one works for me….Frustration is gone and SSIS works.
Thanks a lot. Its works. Wishing you all the best.
I’m not having luck with option B? I’m getting a “AccessDatabaseEngine_x86.exe is not recognized as an internal or external command, operable program or batch” Any hints??
Hi, Bryan. I sounds like it’s not recognizing the path to the installer. Make sure you have put double quotes around your full path to the exe. https://stackoverflow.com/questions/48321639/is-not-recognized-as-an-internal-or-external-command-operable-program-or-bat
Hi Meagan — Thanks for the tip and the pointer, but man am i confused now. Are you saying that screen shot image above in this article has incorrect syntax?
fyi — I’m on a win 7 64 bit machine and my ms office was loaded as 64 bit…
I can’t fathom that all these others folks had no issues using that screen shot as a guide.
When I had problems the first time executing from another directory I moved it to my version of his directory to match syntax perfectly. I even realized the x64 references was in fact X64 on the download file I got, but none of that made any difference (it still wouldn’t fire).
The article you posted requires a pretty interesting move that I wouldn’t have known to do. Did this author and everyone else know to specify executable paths and use a different syntax? Or maybe my machine got a goofy reinstall where the executable dirs are listed properly?
On a different note, when I got stumped, I decided to try to remove the 32 bit stuff that was conflicting and install. That went okay, but then my use of ACE was still not working (localhost website accessing a msaccess db).
I tried to load the 2007 32 bit. That went okay, but then I got an error implying the db name was ”.
I then tried to load the 32 bit for 2010 but ran into a conflict with a one image windows thingy… (seems on reload my IT guys used a bloated all in one windows image that included 32 and 64 bit stuff)… and I was going to have to uninstall all of ms office that had been installed on a reinstall from some magical image that had a bunch of stuff including both 32 and 64 bit stuff.
Worrying I was now in a Microsoft black hole… I went into IIS and went into the application pool and turned OFF the use of 32 bit applications (luck that I remembered I could do that..)
And magically, that allowed the 64 bit ACE driver to fire properly when requests came from the localhost.
And then i reloaded the 32 bit software I had to unload to get the 64 bit ACE driver on the machine…
Dunno what I’ll do if/when I need to use those 32 objects I had purchased a while back.. .upgrade I guess. or try to uninstall all of office or switch to UNIX as that total time invested may be less?!
I don’t know how I got this to work years ago when I first set it up. Maybe I had to do the same, but i didn’t have documented. I do now.
If there is anything you can clarify about why the image above doesnt work that’d be great.. and it may make sense to reference this problem and that link for anyone else who stumbles here hoping for help but gets tossed into a different ms command line grinder.
I’m trying to provide a one-size-fits-all installer for my users. Is there anything they would miss from the 32-bit 2007 version? i.e. Do they need 64-bit? Most have Office 32-bit but a few have 64 or have installed the 64-bit version of 2010 on top of Office 32-bit and then have issues updating Office.
If one is a Power BI user – then the OS (Win 10) should be 64 bit.
In general – and overwhelmingly – the recommended Office install is 32 bit. This can sit on an OS of 64 bit. The reason for the recommendation is that Word, PowerPoint even Access cannot use the benefit of 64 bit plus there are peripherals out there with drivers that are only 32 bit capable.
But if one is a Power BI user then one might also be a real power Excel user – and it is the high end of Excel – millions of rows – that needs 64 bit.
The next part of this is that it means that for Power BI users – for whatever reason it can read/import 32 bit excel data no problem but not 32 bit Access. At least this was true through 2017 and I’m not sure if that has changed or not. My quick & dirty work around was to export the Access table to excel. Otherwise the help of this thread on installing both drivers could be your solution.
In our environment it’s more likely legacy applications that need this connectivity. I’m guessing 32-bit (we almost exclusively install 32-bit Office). Is there any need for the 64-bit database engine?
If all of the applications that must interface with the ACE engine are 32-bit, then you won’t need 64-bit database engine. Power BI is usually 64-bit and therefore requires the 64-bit driver. As mentioned above, hardcore Excel users might be 64-bit. Those who are using 64-bit Office might need the 32-bit ACE for other 32-bit applications (like Visual Studio in my case).
Hi Meagan, I would like to comment on your post made on Feb 27, 2018. I have a very similar situation and need clarification. I developed a program using Visual Studio 2017 that uses an access database. I created the database as an MDB to be more generic. The installer program I created works perfect on my computer which is a a Windows OS 64 bit machine running M.S office 2016 64 bit. But when I run the installer on a users Windows OS 64 bit machine running M.S. Office 2013 32 bit, I get the MS Ace.oledb.12.0 not registered error. My installer program detects the 2013 32 bit office and installes the 32 bit accessdatabase engine.exe. But I still get the same error. It sounds like your saying that If I execute the 2013, 32 bit accessdatabaseengine.exe using the /passive switch I won’t get the error message. Is that correct? Additionally, for my situation, will I need to modify any registry keys? Thank you in advance for your reply.
Thank you for your post, now i have both 32 bit and 64 bit versions of ACE 2016, along with Office 2016 64 bit, SSIS 32 bit from SSMS 17 and VS 64 bit, running like a charm 🙂
I went on installing with Option B. To install ACE 2016, for me, it worked with /quiet.
Thank you very much! It really helped me!
Hi, i was trying to install 64 bit access database.
But im getting an error saying
`AccessDatabaseEngine_X64.exe /passive is not recognized as an internal or external command`.
Any help on this?
Use /quiet instead of /passive. They changed the command for 2016 going forward.
Thank you for this! the /passive on the x86 solved my SSMS import issue on my fresh rebuild!
Hi,
Neither /passive or /quiet is working for me using option B. With /passive I get the original pop up window you mentioned. When I use /quiet, nothing gets installed. I have Windows Server 2012 with 64 bit office and I am trying to install the 32 bit AccessDatabaseEngine.exe from the Microsoft site linked above.
Any other suggestions?
What version of Office?
Thanks! Option 2 with the “/quiet” parameter worked perfectly!
Great solutions, thank you.
Hello Mega,
I am trying to import excel 2016 version file to the SQL Server 2012, i am getting the Oledbb 12.0 error, i have installed the Access Database engine but still i am getting the issue
Do we need to make any registry changes when using the two 2016 installers on 64-bit Office 2013? In my HKLM\Software\Microsoft\Office I see folders for 11.0, 12.0, and 15.0, not 14.0 as mentioned in the post.
Oops, I have a 16.0 folder too!
You are Amazing ,k Thank you!!!!!!!!!!!!!!!!!!! <3
I have office 2019 64 bit with the same problem ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine” . I solved following Megan’s procedure,2010 Access database engine, but I didn’t have to change “HKEY_LOCAL_MACHINE SOFTWARE”, I confirm however that in the prompt, it must be used quiet, and not passive
Thanks Megan you have solved my big problem.
Very good job
Many many thanks Meagan !!!
THANK YOU!!!!! In the middle of doing an annual update to an SSIS package my computer was upgraded from Win7 to Win10, and Office 2010 to Office 365. And then my SSIS package could no longer read the excel file it needed. Installing the 32-bit version with /passive allowed it to read the file again.
Thank you! That save me a lot of trouble!
This rocks, I appreciate !!!!
I’ve been trying for days to figure this out. Thank you so much for posting this. There are so many websites trying to help but this is the only one that worked for me. Thank you so much!
Thank you for sharing this tips. I was looking for a solution for over a month. I use SQL Management Studio with Import & Export Wizard. After I switch to Office 64bit, it won’t work. This safe trick saved me so much hassle.
So glad it helped!
Thanks a lot,
Option A: Install the 2007 Office System Driver solved my problem.
Thanks very much.. It solved my problem. i was using office 2016 so i installed both 32 bit and 64 bit driver.
Is there anything else I have to do after typing the command on CMD in option 2?Just the pop up popped and nothing got installed. Do I need to restart my PC?
I can’t remember if it requires a restart, but it’s always worth a try. Make sure you used the /passive switch instead of quiet for newer installations (this post was written over 3 years ago).
thank you, you’re so amazing Meagan! really helpful!
Thank you so much.
THANK YOU!!! I’ve been beating my head against the wall trying to figure this out.. i have 64bit office 2010 on windows 10.. tried with VB studio 2015 and 2019.. got it working in ’15.. will work with that.
Hi Meagan ! I have an application developed in vb6 (32-bit) running MS-OFFICE 32-bit on which I am using Microsoft.ACE.12 provider to read from an Excel data… The program works perfect on my 32-bit windows running MS-Office 32-bit.
However, it does not work on another PC of m office. That has MS-OFFICE 32-bit installed on Windows 8.1 64-bit.
My doubts are:
1) Which version of the ACCESSDatabaseEngine should I install in my Office PC (Windows 64-bit ; Office 13 -32 bit) in order to make my program to read the Excel data.
2) Whether the Accessdatabaseenginer that I should be installing is required to be run with (/passive) option
3) Will my application break if I install BOTH the 32 bit and 64 bit versions of the 2010 AccessDriver with the /quiet option on my Office PC (Running Windows 64-bit ; MS-OFFICE 32 bit).
Thanks in advance.
I don’t do VB6, but I’m pretty sure you won’t break your application if you have both drivers installed. I always have 64-bit Office installed because I use Power Pivot and need more memory in Excel than 32-bit supports, so I can’t help you there. 32-bit is still the default, I believe. If you are only installing one version of the ACE driver, you won’t have to use the /quiet or /passive switch (depending on version). That is only needed when you have one installed (e.g., 32-bit) and then need to install the other ((e.g., 64-bit). Overall, you should just need to match the bittedness of the client and driver.
Hi Meagan. Brilliant, thanks very much. I’m running Windows 10 with Access as part of Microsoft 365. I’m developing in VS2019 which (apparently) runs as 32-bit, so had to keep compiling and running my code natively to make use of the 64-bit drivers. Running the 32-bit database engine installer with the /quiet option certainly did the trick, and saves a lot of messing around. Access still works too, so no conflicts.
Megan, what if accessdatabaseengine_X64.exe installed successfully, and SQL server Management studio (version 17) still gives an error message “The OLE DB provider “Microsoft.Ace.OLEDB.16.0″ has not been registered”? The following commands have been executed:
USE [TEST01]
go
–ADD DRIVERS IN SQL INSTANCE
EXEC [TEST01].dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.16.0′, N’AllowInProcess’, 1
GO
EXEC [TEST01].dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.16.0′, N’DynamicParameters’, 1
GO
The error message is received when I try either one of the 2 queries below:
(1)
SELECT *
FROM OPENROWSET(‘Microsoft.ACE.OLEDB.16.0’,
‘Excel 16.0; Database=C:\Test\MedstatOPDec2020.xlsx’, [Sheet1$])
(2)
SELECT *
FROM OPENDATASOURCE(‘Microsoft.ACE.OLEDB.16.0’,
‘Data Source=C:\test\MedstatOPDec2020.xlsx;Extended Properties=Excel 16.0’)…[Sheet1$];
Check out the code in this post and see if that helps: https://datasavvy.me/2013/11/12/fun-with-openrowset/
Meagan, the repeated installation attempts did not lead to the creation of Microsoft.ACE.OLEDB.16.0 entry under Server Objects->Linked Servers->Providers in SSMS on the client, nor in the registry of the client under HKEY_LOCAL_MACHINE -> SOFTWARE -> Microsoft -> Microsoft SQL Server -> MSSQL11.MSSQLSERVER -> Providers (described in your post above). I left a reply here yesterday close to 2 pm CT, it has not been published. Will you have any idea how this can be resolved?
Hi, Art. I’m sorry I don’t have other ideas how to resolve your issue.
Worked like a magic for SSIS.. able to install 32 bit and package running successfully
What solution fixed your problem? We had a recent install of office 365. From then I am getting this ACE.OLEDB.12.0 provider issue from table to Excel or from excel to Table data flow. Please advise as it’s very urgent
Hola, tengo el mismo problema, en un pc que tiene office 365 me arroja el error ACE.OLEDB.12.0 , pero en uno que tiene instalado Microsoft Office 2013 si funciona, y lo que pasa es que en el equipo que tengo office 365 no instalan el paquete office 2013 por politicas de la empresa y seguridad
It works, Thanks Meagan!!!
Four years later and still helped my cause. Thank you!
THANK YOU!!!
I’ve been hunting for this all morning after I got a newly imaged laptop from my company.
Installing it from the command line worked like a charm!
Thanx! This works! Couldn’t import data from xslx (over 200k records) to SQL Server, using MSSMS 18.2, said Microsoft OLEDB 16 not registered. This magic worked, I was able to import.
Thank you so much! It worked for me after having issues with running SSIS Excel Connection Manager. I wasn’t able to find the mso.dll location as indicated but it still seems to work for me!
i didn’t need to run the command line. but your post gave me the idea to try both 32 and 64 bit. i originally only had 64 bit installed and was still getting the error. thanks to your post, i installed the 32 bit as well and… finally!
thank you.
Thanks heaps for this.
THANK YOU THANK YOU THANK YOU!!!!!!!!!!!!!!!!!!!! I have been looking for a solution to this problem for over a day now!!!!!!!!!!!!!! I have 64-bit Office installed and am running Visual Studio 2019 with SSDT and the SSIS extension installed. VS and SSDT and the SSIS Extension I believe are all 32-bit applications and I could not read an Excel file. Your second option above works. (install from the command line with the /quiet option. /passive yielded the same error. /quite succeeded!!! THANK YOU. Mike
Hi Mike. What specific path did you install the 32 bit Ace provider? Thanks.
OMG !!! Been looking for this answer for years!! The silent install worked a treat for the 32bit library !
It worked for me too ! “/quiet” for 2016 version. Thank you. Although, I didn’t have to delete/rename the mso.dll
Is there any information why the mso.dll should be deleted/rename in the registry? In the mentioned office version “…\Microsoft\Office\14.0\…” no mso.dll entry is available in my registry. I only found the mso.dll entry in the “…\Office\15.0\…” and “…\Office\16.0\…” folders. Should the mso.dll removed from all versions? Or only from the installed version with quiet/silent option?
I got some update errors for microsoft x64 security updates, but after a restart it looks like the updates could be installed. I don’t know if this was a side effect of the installation with quiet/silent.
I’m not aware of the silent install having that side effect.
Brilliant. Thanks!
Downloaded both AccessDatabaseEngine.exe & the _x64 version. Only ran the first from the cmd line. No change to the registry, and it works now.
I had to use this last year when we were on Office 14 or so…. and here I am again doing my annual update of my hated SSIS package and have to refer to this again. THANK YOU, this is continually useful. I did both /passive and /quiet just to be safe. 🙂
For Office 16 (Office 365) there isn’t a key similar to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Common\FilesPaths in the 16.0 folder.
I did find the following, however:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\16.0\Common\FilesPaths
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Microsoft\Office\16.0\Common\FilesPaths
I also found some for older versions than 16.0
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\12.0\Common\FilesPaths
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Common\FilesPaths
I’m leaving those alone for now to see how it turns out… but will start carefully pruning these entries (especially the 16.0 ones) if it seems necessary!
Thanks for this, this has been an on and off pain for years. You need to download the MSAccess redistributable (I downloaded office install initially). Just run accessdatabaseengine.exe / quiet and that is it, no registry hacks required. Works fine in SSMS Export where it hadn’t previously. I have Office 64 bit installed but 32 bit Visual Studio and SSMS.
2024- This worked like a charm! Thanks.
The links are not valid tho. This one worked for me: https://www.microsoft.com/EN-US/DOWNLOAD/confirmation.aspx?id=54920
Thanks for this post! The magic works for me…. and saves my day!
Another happy ‘customer’.
Thank you.