Converting Access Database to MSSQL Database Using Microsoft SQL Server Migration Assistant for Access

Question

I am trying to convent a Ms Access Database to MS SQL 2012, using Microsoft SQL Server Migration Assistant for Access version 6.0 but each time i try to convert it an error pops up. any ideas how i can solve the problem i reinstall the program Microsoft SQL Server Migration Assistant for Access , and it worked just ones and after that i have the same error. any help will be most appreciated

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)). 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. You can also consider updating your connectivity components from http://go.microsoft.com/fwlink/?LinkId=197502. An error occurred while loading database content.

Washburn

did you made the 32/64 bit checks suggested by the error message itself? – Terence

yes i did made the changes the wired thing is that it worked. just ones – Washburn

Answer 1

I remember this error. I had to find the EXE for 32-bit SSMA even though I was running 64-bit windows. The default installed location was:

C:\Microsoft SQL Server Migration Assistant for Access\bin

And the filename was:

SSMAforAccess32.exe

Benoni

We've had success with this approach, wheras the installation of the 2010 runtime did not work for us (our environment is Windows 7 64-bit, SQL Server 2014, Access 2013). – Tabular

Answer 2

Just to complete the other side of the story...

If you have a 64-bit setup, you might have to go the other direction
and install the 64-bit MSAccess 2010 engine.

I'm running Access 2013, and I believe it is 64-bit. But the SMAA tool threw the 80040154 error.

• I think SMAA can't or doesn't use the 64-bit connectivity drivers that come with 64-bit Access 2013.

So using the 32-bit SMAA didn't help.

• Installing the 64-bit 2010 engine did.

I got it as a free standalone download here:

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

After the quick install of this, I simply started
SMAA 64-bit again,
and the wizard was successful the first time.

---

thanks to : https://social.technet.microsoft.com/Forums/itmanagement/en-US/0249eebf-14bd-45f6-9bca-3b42395a3d13/ssma-60-error-retrieving-the-com-class-factory-for-component-failed-due-to-the-following-error?forum=sqlservermigration

Markos

Believe it or not installing the 2010 stand-alone tool, fixed my issues on Access 2016 on Windows 10. – Hutcherson

The issue here is that the MS SQL Server developers that developed the migration tool never had a full install of MS Access on their system so they've introduced dependancies on components that are only installed with the MS Access runtime environment. – Homoeo

This also worked for me. Windows 10 64bit, SMAA 64Bit, SQL server 2019 trial 64bit?, Office Access 32bit, Microsoft Access Database Engine 2010 Redistributable 64bit – Prehistory

Answer 3

[SOLVED]

I encountered the same error while using SSMA v7.7.0 for migrating from MS-Access 2016 DB to SQL Server 2017. Fortunately, I got it fixed by installing Microsoft Access Database Engine 2016 Redistributable

Brawner

Answer 4

with regards to installing the runtime environment of Access to get around the 80040154 error from SSMA: With Access 2016, I found that the runtime environment would not install because I already had Access installed. I needed to deinstall Access, then install the runtime only version. Then, SSMA would run without the 80040154 error. Total pain tho.

Mahout

Answer 5

If you're trying to read/write data from/to MS Access database file via .NET application, try to switch the project to x86 architecture. It helps in my case

Lyford