How to use SSMA for Access on O365 ProPlus?

Question

In the latest version of Access for O365 ProPlus there is no SQL Upsizing Wizard tool. I have read that this method was deprecated in favor of Microsoft SQL Server Migration Assistant (SSMA). I am here to report that it is impossible to install this tool when running the latest version of O365 ProPlus.

When the installer for SSMA is run it complains that it cannot find the DAO libraries that it needs, despite Access being installed. There is a download button there to save you! That links you to the Access Runtime for 2016. Unfortunately, the Access Runtime installer clearly states that it won't run because O365 ProPlus is installed. Full bork.

Is there a way I can continue to use SSMA for Access in the future?

Is there a different tool I should be using for exporting Access tables to SQL?

I captured the whole debacle in one screenshot:

SYSTEM DETAILS:

OS: Windows 10 Pro 1909 18363.535 64-bit
OFFICE: O365 ProPlus Version 1902 64-bit
ACCESS: 16.0.11328.20492 64-bit

Answer 1

It will let you complete the install but then later, when you go to use it you see a similar error:

Access Object Collector error: Database
     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)). Please make sure that you are using the correct version of SQL Server Migration Assistant. If you have 32-bit Microsoft Access installed, then you will need to use 32-bit version of the Migration Assistant. In order to use 64-bit version of Migration Assistant, you will need to install 64-bit Microsoft Access components available at https://go.microsoft.com/fwlink/?linkid=834402.
     An error occurred while loading database information.

That link at the end brings me back to the Access 2016 runtime which cannot be installed alongside O365 ProPlus

Answer 2

Here is an article that describes your problem at the end (4th paragraph from the end).  The suggestion is to install a different version of the Access runtime than 2016.  I would try installing the 64 bit 2013 runtime and then SSMA.

https://techcommunity.microsoft.com/t5/Microsoft-Data-Migration/Access-connectivity-components-for-SSMA/ba-p/391070

-Bruce

Answer 3

Thanks Bruce!

The solution was to install Microsoft Access Database Engine 2010 Redistributable 64-Bit

https://www.microsoft.com/en-us/download/details.aspx?id=13255