Access connectivity components for SSMA

This post has been republished via RSS; it originally appeared at: Microsoft Data Migration Blog articles.

 

We often receive questions about what is really needed to work with Access databases in SQL Server Migration Assistants (SSMA) for Access, so I thought I’ll do a quick run through what is actually required.

 

The typical error message that you will get in SSMA, if required components are missing will look like this:

 

“Retrieving the COM class factory for component with CLSID {CD7791B9-43FD-42C5-AE42-8DD2811F0419} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)). This error may be a result of running SSMA as 64-bit application while having only 32-bit connectivity components installed or vice versa. You can run 32-bit SSMA application if you have 32-bit connectivity components or 64-bit SSMA application if you have 64-bit connectivity components, shortcut to both 32-bit and 64-bit SSMA can be found under the Programs menu.”

 

SSMA communicates with Access through Data Access Objects (DAO), which is implemented by ACEDAO.DLL and registered as a COM class object with the ID {CD7791B9-43FD-42C5-AE42-8DD2811F0419}. Since SSMA is built on top of .NET Framework (i.e. managed code) – it needs a managed wrapper, that simplifies interaction with the COM component. This wrapper is implemented by managed Primary Interop Assembly (PIA). Given this, all you need to be able to load Access databases in SSMA is:

  • Registered DAO COM class object
  • Managed Access PIA library

The question now is where do I get these… Although it’s pretty straight forward, there are some caveats. Ideally you would just install Microsoft Access Runtime redistributable from one of the following links:

Or you can get a smaller Microsoft Access Database Engine Redistributable package:

This has all needed components as well, but it is only available for Access 2010, as of now.

 

Whichever option you prefer – make sure to pick the right platform architecture. We strongly advise to use 64‑bit flavor simply because you can quickly run out of memory while using 32‑bit SSMA with a decent sized database. All this looks fairly simple so far, but here comes the first caveat: you can only have one flavor of Office installed at the same time for the same version. This means that if you have 32‑bit version of Office 2016 on the machine, you will not be able to install 64‑bit Access 2016 components, which is pretty bad for the SSMA (we want to run 64‑bit, remember?). Typical error message you will see is:

 

“We can’t install the 64‑bit version of Office because we found the following 32‑bit programs on your PC…”

 

The workaround to this problem is to install different version of Access components, compared to the version of Office you have already installed. For example, you have 32‑bit Office 2016 installed and want to use 64‑bit SSMA – in this case you will install 64‑bit Access 2013 Runtime.

 

Even if you have 64‑bit Office installed and will try to install same 64‑bit Access components of the same version – you may hit another issue related to Office Click-to-Run installation. The typical problem is that it does not allow to install an Office components through MSI side-by-side with Click-to-Run. The error message will say something like:

 

“Windows Installer and Click-to-Run editions of Office programs don’t get along for this version, so you can only have one type installed at a time. Please try installing the Click-to-Run edition of Office instead, or uninstall your other Click-to-Run based Office programs and try this installation again”

 

Since there is no Click-to-Run version of Access Runtime redistributable available and uninstalling existing Office programs is not an option, workaround in this case is the same – just use different version of Microsoft Access Runtime.

 

Long story short, if you have troubles loading Access databases in SSMA – go over all available Microsoft Access Runtime redistributables, start with 2016 and downgrade until you find the one that works in your specific case and remember – you want 64‑bit flavor!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.