SQL Server Migration Assistant DLL errors when trying to import any Access database-CodePudding

I'm currently trying to re-import an Access database into a SQL Server database (to update a demo with the most recent data). I've previously been able to run that import on this machine with SSMA, but since the last time I've imported, I've changed the account Office 365 is registered under, and also installed an updated version of the SSMA program. That's all I can think of that's changed since my last successful import. Something apparently broke, because now when I try to connect to an Access database in the SSMA import wizard (the error occurs when connecting to the Access database, not when connecting to SQL Server), I get the following 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: 8007045a A dynamic link library (DLL) initialization routine failed. (Exception from HRESULT: 0x8007045A). An error occurred while loading database content.

Here's the full content of the log file:

[Mandatory: Generic] [16268/4] [2022-09-29 21:06:40]: SQL Server Migration Assistant for Access v9.1.22233.1001
[Mandatory: Generic] [16268/4] [2022-09-29 21:06:40]: Starting assembly: SSMAforAccess, Version=9.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Created: 08/21/2022 14:03:24
Last Modified: 08/21/2022 14:03:24
OS Version: Microsoft Windows NT 10.0.19044.0
OS architecture: x64
Processor architecture: AMD64
Current culture: en-US
UI culture: en-US
[Error: Collector] [16268/20] [2022-09-29 21:06:59]: Access Object Collector: An error occurred while loading database content.
[Error: Collector] [16268/20] [2022-09-29 21:06:59]: Exception: Retrieving the COM class factory for component with CLSID {CD7791B9-43FD-42C5-AE42-8DD2811F0419} failed due to the following error: 8007045a A dynamic link library (DLL) initialization routine failed. (Exception from HRESULT: 0x8007045A).
 site: System.MarshalByRefObject AllocateUninitializedObject(System.RuntimeType)
 source: mscorlib
   at System.Runtime.Remoting.RemotingServices.AllocateUninitializedObject(RuntimeType objectType)
   at System.Runtime.Remoting.Activation.ActivationServices.CreateInstance(RuntimeType serverType)
   at System.Runtime.Remoting.Activation.ActivationServices.IsCurrentContextOK(RuntimeType serverType, Object[] props, Boolean bNewObj)
   at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck)
   at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark)
   at System.Activator.CreateInstance(Type type, Boolean nonPublic)
   at System.Activator.CreateInstance(Type type)
   at Microsoft.SSMA.Framework.Access.DaoUtils.DaoUtilities.ConnectToDatabaseImpl(String fileName, Boolean readOnly, String password, Nullable`1 workgroupPolicySettings)
   at Microsoft.SSMA.Framework.Access.DaoUtils.DaoUtilities.TryConnectToDatabase(String fileName, Boolean readOnly, String& password, PasswordAsker passwordAsker, Nullable`1& workgroupPolicySettings, WorkgroupPolicyAsker workgroupPolicyAsker)
   at Microsoft.SSMA.Framework.Access.Generic.AccessConnectionUtilities.ConnectToMdbFile(XNode xMdbFile, Boolean readOnly, IUIAskPasswordProvider askPasswordProvider, IUIWorkgroupPolicyProvider workgroupPolicyProvider)
   at Microsoft.SSMA.Framework.Access.Collector.Loaders.MDBFileLoader.LoadChildren(XNode xNode)

Office 365 is 64-bit. SSMA appears to also be 64-bit (it's in C:\Program Files\Microsoft SQL Server Migration Assistant for Access\bin\SSMAforAccess.exe). SQL Server 2019 Developer Edition is also 64-bit. I've tried repairing the Office installation and uninstalling and reinstalling SSMA... no luck.

Any suggestions for how to fix the problem (or a different group I should post the question to)? Many thanks in advance!

[Edit - further details]: restarting the computer didn't do anything.

A regedit search for CD7791B9-43FD-42C5-AE42-8DD2811F0419 indicates that it's for DAO.DBEngine.120. (it also appears to have several entries under ClickToRun... not sure if that's relevant). There is no value set under any of these keys:

Computer\HKEY_CLASSES_ROOT\WOW6432Node\CLSID{CD7791B9-43FD-42C5-AE42-8DD2811F0419}
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Classes\WOW6432Node\CLSID{CD7791B9-43FD-42C5-AE42-8DD2811F0419}
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Classes\Wow6432Node\CLSID{CD7791B9-43FD-42C5-AE42-8DD2811F0419}
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Classes\CLSID{CD7791B9-43FD-42C5-AE42-8DD2811F0419} ...there also doesn't appear to be any reference to any DLL anywhere in the registry, directly associated with that CLSID.

CodePudding user response：

Reinstalling the Microsoft Access Database Engine 2016 Redistributable seems to have fixed the problem. (not sure how it got broken in the first place).

The key to finding the answer was finding out that the CLSID resolved to DAO.DBEngine.120 in the registry, so I started searching for that.