Installing the Microsoft.ACE.OLEDB.12.0 Provider for Both 64-bit and 32-bit Processing

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.

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.

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?