Error Installing SSMA

[Charax]

I installed file SSMAforAccess_10.1.0_x86.msi; I accepted the recommended upgrade to v.10.2 during installation. The installation ran smoothly,

My computer has Microsoft Access 2019 MSO (Version 2505 Build 16.0.18827.20102) 32-bit on Windows 10 Pro I also have SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19045: )

The application is a legacy Access FE/BE split database. I want to convert the Access back end to SQL Sever BE.

Here's the problem. I used SSMA Migration Wizard to load the back end .MDB data file (that contains 61 tables plus a few maintenance queries). The wizard pops an error dialog "There is nothing to process." The wizard's last Migration Status dialog shows all red Xs.

The Metadata Explorer shows the database has loaded, but contains no queries and no tables. When I mouse click the database name (Data_SNP4.mdb), I receive this error:

An error occurred while loading database information.
Unable to cast COM object of type 'Microsoft.Office.Interop.Access.Dao.DBEngineClass' to interface type 'Microsoft.Office.Interop.Access.Dao._DBEngine'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{00000021-0000-0010-8000-00AA006D2EA4}' failed due to the following error: Library not registered. (Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED)).

The almost exact same error appears twice in the SSMA Output frame, but titled slightly different as "Access Object Collector error: Database Unable to cast COM object...."

Researching the error on Google indicates it may be a registry problem since I am using Office 2019 32-bit instead of Office 365 64-bit, but I found no solution.

I hope someone knows how to solve this! Thanks in advance for any suggestions.

Cheers,

Charax

​

 

[theDBguy]

Just a guess but does the wizard work with ACCDBs? If so, maybe try converting the MDB to ACCDB first.

 

[Charax]

Just a guess but does the wizard work with ACCDBs? If so, maybe try converting the MDB to ACCDB first.

Thanks for a thoughtful suggestion. I saved the legacy MDB file as ACCDB and received message that it was converted to Access 2007 format:

However, when I ran SSMA, the same identical errors popped as with an MDB file.

 

[theDBguy]

Thanks for a thoughtful suggestion. I saved the legacy MDB file as ACCDB and received message that it was converted to Access 2007 format:
View attachment 120061

However, when I ran SSMA, the same identical errors popped as with an MDB file.

That's unfortunate. But to determine the actual cause of the problem, the first part of my question was to verify if SSMA works with regular (not converted) ACCDB files first. Otherwise, I don't think we can be certain if the problem is your MDB file or your install of SSMA. Luckily, even if you can't use SSMA to migrate your MDB to SQL Server, it's still possible to do it manually.

 

[Pat Hartman]

Did you install the correct version of SSMA? It needs to be the same bitness as your Office installation.

 

[Charax]

That's unfortunate. But to determine the actual cause of the problem, the first part of my question was to verify if SSMA works with regular (not converted) ACCDB files first. Otherwise, I don't think we can be certain if the problem is your MDB file or your install of SSMA. Luckily, even if you can't use SSMA to migrate your MDB to SQL Server, it's still possible to do it manually.

Good idea so thanks, theDBguy. I created a simple, totally fresh ACCDB database in Access 2019 with multiple tables, some queries, relationships. I set the General Settings default file format for Blank Database to Access 2007-2016.

Alas, an attempted SSMA conversion of the virgin database to SQL Server 2019 popped the exact same errors mentioned earlier. I'm thinking there must be some Dao.DBEngineClass driver I don't have (or should not have installed or registered).

Google AI suggested to install Microsoft Access Database Engine 2016 Redistributable but this sounds contrary to common advice that one cannot have both full Access and the Redistributable installed on same machine.

 

[Charax]

Did you install the correct version of SSMA? It needs to be the same bitness as your Office installation.

Pat, is there a way to check 32-bitnesss for SSMA? On the download page there are now two choices,
SSMAforAccess_10.2.25135.msi OR SSMAforAccess_10.2.25135_x86.msi

My Office is 2019 Pro 32-bit. To install SSMA, I used filename SSMAforAccess_10.1.0_x86.msi (downloaded several days ago). Maybe I should try the new 10.2 file, but during install I accepted the installer's recommendation to accept the upgrade to v.10.2. And the Help>About says I have v.10.2.

 

[Gasman]

X86 will be 32 bit.

 

[Charax]

So, I think I now understand why SSMA would work on my computer once I saw the following error message:

It popped when I attempted to install the Access 2016 Runtime (32-bit) while I had Microsoft Office 2019 Pro MSO.

Numerous Google search references mentioned to the need to install the Access 2016 Runtime in order to get the necessary DAO driver registered. That can't be done while you are running a Microsoft Store version of Microsoft Office 2019 Pro (32-bit). In other words, I had used Office 365 to install Access 2019 (32-bit).

In a brute force fix, I completely deleted Microsoft Office 2019, then ran the Access 2016 Runtime installer. That apparently installed the necessary DAO driver to allow SSMA 32-bit to run error-free. After confirming SSMA was OK, I tried once again to install Microsoft Office 2019 with no success -- popping that We found a problem! error (above) because Access 2016 Runtime and Microsoft Office 365 are incompatible.

I deleted the Access 2016 Runtime and then successfully reinstalled Microsoft Office 2019 using Microsoft Store's Office 365 installer. Now Access 2019 (32-bit) and SSMA (32-bit) are working well. Unfortunately you can't reinstall Access 2016 Runtime.

An added benefit is that, after using the new SSMAforAccess_10.2.25135_x86.msi) installer file, SSMA has a top border that announcing it is 32-bit.

For me, SSMA is working and the problem is solved.

 

[theDBguy]

For me, SSMA is working and the problem is solved.

Glad to hear you got it sorted out. Good luck with your project.