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 (8)

  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:

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

  5. Rickard Olsson says:

    Shame on you!
    How could you deliver a tool with so many complex requirements?
    Instead of waisting days of work on trying to get the tool running it is probably more effective to do the whole process manually after loading the data into SQL Server??
    You save a small amount of time (some hours – maybe a week) in development giving us trying to use it tons of hours to spend how to get it up running!
    Which at the bottom line cost a lot of the enduser community (your customers -an mine!! ) lot of money. BUU!

  6. Klaus Oberdalhoff says:

    If downloading either – Microsoft Access Database Engine 2010 Redistributable or – Microsoft Access Database Engine 2016 Redistributable

    and ( – now the part that is not written down – )
    open cmd-line and install it with the runtime switch /quiet

    Access…._X64.exe /quiet

    then it WILL install without message and without complain

    1. Rob Bowman says:

      For me, I got the same error message – using the /quiet made no difference

  7. I really appreciate the effort taken to write this blog post but I’m afraid I haven’t been able to create a single VM with a working version of SSMA for Access 64bit and Office 2016 64bit. I have tried for the past couple of days, across three different VMs (two Server 2016 and one Windows 10). I have followed the steps set out in this blog and also used the Office Removal tool many many times in order to start again. I have tried all 64bit versions of the Access runtime and Access redistributable packages..

    It would be great if you could post the sequence of steps too achieve this from a clean OS install. Would be even better if you could make a docker image available – but I guess that may not be possible due to licensing.

Skip to main content