Access connectivity components for SSMA


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:

  1. Registered DAO COM class object
  2. 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:

Which has all needed components as well, but 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!

Comments (4)

  1. Weston Argo says:

    I owe you my first born child for this advice. Thank you.

  2. Gopal says:

    In my case, I initially used [SSMA 7.5, SQL Server 2016 and Access 2013 (32 Bit) and Office Runtime 2016 32 bit]. SSMA was unable to display tables and I had the same COM error. Then tried installing Office Runtime 2013 64-bit (install failed with ‘We can’t install the 64‑bit version of Office because we found the following 32‑bit programs on your PC’)

    Finally was able to install Office Runtime 2010 64-bit. The COM error still appeared with SSMA 7.5 since I had to use 7.5 to migrate to SQL SERVER 2016. The same COM error vanished when using SSMA 6.0 with SQL SERVER 2014. I am finally going to use SSMA 6.0 with SQL AZURE as we decided to use Azure. Hopefully that goes smooth.

  3. This doesn’t work on my system, which has SSMA 7.5 and Office 2010 installed.

    I can’t install the 64-bit components – they all come back with the response that I have to remove Office 2010 32-bit and re-install 64-bit Office, which is not an option in my case.

    And SSMA 7.5 indicates that it can’t extract the database metadata because it needs the 64-bit components.

  4. Ken Evans says:

    I suggest that you repackage this blog page and add it as a “readme-first” document in SSMA and Access 2016 runtime.
    My trial-and error attempts have cost me days of my time and wasted my money.
    See this link:
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c469f5e0-a5b1-4c9d-a568-bb9af26002ee/ssma-75-installation-problems?forum=sqlservermigration

    Seems to indicate an amateur approach to development, testing and product dcumentation.

Skip to main content