Error Installing SSMA (1 Viewer)

[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.

 

[Pat Hartman]

Numerous Google search references mentioned to the need to install the Access 2016 Runtime in order to get the necessary DAO driver registered.

This seems to be old information. I don't think it is still necessary.

 

[Charax]

This seems to be old information. I don't think it is still necessary.

Thanks for your note, Pat. It is my experience that, if you have an O365 installed version of Access, you cannot install Access 2016 Runtime. Trying to do so causes the We found a problem! error (above).

Apparently, to install SSMA, you must have one or more drivers installed by Access 2016 Runtime if on a machine running an O365 installed version of Access.

Is there another method to get the driver(s)? Or perhaps there is a Win Registry entry that can be added/deleted to facilitate a SSMA install?

 

[Pat Hartman]

Apparently, to install SSMA, you must have one or more drivers installed by Access 2016 Runtime if on a machine running an O365 installed version of Access.

This is what I was saying is no longer necessary. I was able to simply install SSMA last year on my new computer. No Runtime needed to be loaded first. It's really too bad that most of the crap we find when searching for solutions doesn't have any dates associated with them so we can't ever tell how old the recommendation is.

 

[Charax]

Pat, do you have both Access and Access 2016 Runtime installed on same machine?

For me, unfortunately, installing SSMA 32-bit was not possible on three of my Win 10 (64-bit) computers. One is a new Dell tower but I continue using Win 10.

After many frustrating hours and multiple attempts, the only solution for me, was the clunky, brute force approach:
Uninstall Office > install Access 2016 Runtime > uninstall Access 2016 Runtime > reinstall Office

Note that once I reinstalled Office, Access 2016 Runtime could not be installed.

Sadly, this seems to confirm the frequent mentions on Google search that Access 2016 Runtime drivers are perquisite.

 

[Pat Hartman]

No. I do not have any runtime engine installed. I am working on a development machine. Last November, I got a new computer. Sadly with Win 11. Starting from scratch, I installed the Virus program. Office 365 subscription, not from disk. SQL Server. SSMA. In that order. And then spent hours, days trying to get a configuration similar to the dead computer. All the data came from the old hard drive via a drive to drive copy.

You can't install the runtime for whatever full version you have involved. The runtime, if you install one, must be for an older or newer version of Access.

YOU don't need the runtime technically, so except for wanting to install SSMA, you shouldn't even consider installing it on your dev machine.

Perhaps, you installed an old version of SSMA rather than the newest version because there were several years during which installing SSMA was a royal PITA and did require the preceding step of installing a Runtime in order to get the correct version of ACE that the SSMA needed. This was never a problem with Jet since Jet was always distributed as a separate engine even when installed with Office. In fact Jet used to be installed as part of Windows back in the previous century.

If you've got everything back together again, chalk it up to MS being less than helpful and clear with this type of stuff. Complaining to them will get you nowhere except that I like to do it because the should know about the pain they cause us developers when they don't consider us to be lifeforms.

 

[Charax]

Thanks for note, Pat. Your installation sequence makes me think that perhaps Windows 11 has the missing drivers.

With SSMA installed on my backup computer, I'm going to leave well enough alone. I didn't mind deleting and reinstalling Office 2019 on the backup machine so I could try the Access 2016 Runtime kludge. I'm not about to do that deed on my primary. Whenever I'll need to use SSMA, I'll go to my older backup machine.

An Office reinstall could screw up a stable machine.... I'm very concerned about the short predicted lifespan of Outlook (Classic). Don't know what to do when it expires. A fully indexed set of all my email back to 1997 is such a fantastic resource, but that's anther topic.

 

[Pat Hartman]

Don't know what to do when it expires.

i keep looking for a mail interface i actually like. classic outlook is close but ms keeps removing features i like and use. new outlook is a piece of poo but for inexplicable reasons, it has some useful ability to filter out trash that classic doesn't contain.