Blog

SQL Server Migration Assistant for Access nightmare

Getting from Access to SQL is not as much fun as it should be and it seems that it gets harder with each release. The upsize tool in Access 2013 is gone and the recommended way is now to use the SQL Server Migration Assistant (SSMA).

Like a lot of people, I run Windows 64-bit OS with 32-bit Office (which is Microsoft's recommendation. When running SSMA, I kept hitting 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: 80040154. 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.


 

Based on this post, I'd regsvr32 Da0360.dll and added that folder to my environment's PATH but neither helped.  For others, running the 32-bit version of SSMA was the suggested fix but this also didn't work for me.  I almost went down the path of setting corflags but just didn't feel that that was my issue.

Thinking it was all about 32bit and 64bit, I pulled out my tablet which is Win 8.1 32-bit but it had the exact same error.  Finally, I found this post which had the fix which is to install the Microsoft Access Database Engine 2010 Redistributable.  The post used the 2007 edition but I used 2010 and it worked fine.  I think it may work with Microsoft Access Database Engine 2013 but once it worked with the 2010 edition, I moved on.  I'm not sure why having Access 2013 isn't enough but I know a lot of other people are struggling with this issue and not getting much support from the SSMA team.  In fact, support for that product seems really lacking.  On the SSMA 5.2 version page (5.3 is out and the one I installed) there were several comments (some very frustrated) with my exact issue but no response from the Microsoft team.  I emailed their help address which replied with an auto-response to open a ticket which I did, but still no response.  Hopefully this post will help someone and you won't feel so alone. smiley

 

Return TopTrackbackPrintPermalink

Comments

Got something to say? Join the discussion »
    Posted @ 5/4/2016 9:18 PM by Michael from MD
    Michael from MD's avatar

    THANK YOU FOR THIS!

    Posted @ 1/10/2016 7:25 PM by SLaaaa
    SLaaaa's avatar

    Perfect. Thanks for posting the solution!

    Posted @ 10/13/2015 2:04 AM by Andre
    Andre's avatar

    I ran into a problem trying to migrate an ACCESS DB to SQL using the SQL migration tool v6 on Windows 10. The following message displayed: no object found to migrate, along with a 20-digit or so error code. I got around this by being able to download version 5.3 of the AQL migration tool from Microsoft and it worked perfectly for me!

    Posted @ 9/18/2015 8:58 PM by sanderson
    sanderson's avatar

    Thanks John Granade. Installation of Microsoft Access Database Engine 2010 Redistributable worked for me too (no impact on functioning of Access databases). 32-bit SSMA did not work by itself.

    Posted @ 8/11/2015 11:18 PM by Al Conte
    Al Conte's avatar

    On the Microsoft forums, it was suggested to read your blog for my problem.

    Has installing the Microsoft Access Database Engine 2010 Redistributable adversely affected anything else? I have many other Access DB's, some mission critical. They do not need to have an SQL backend, Would they have problems?

    (I am new to Access...)

    Thanks,

    Al

    Posted @ 8/3/2015 4:53 AM by Edward
    Edward's avatar

    A big help! Thank you for sharing this simple solution to the problem above.

    Posted @ 4/28/2015 10:20 PM by Steven
    Steven's avatar

    Thank you for putting this post together! I tried some of it but the corn post helped the most because I found the 32bit exe version of SSMA in the bin folder.

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

    Posted @ 8/29/2014 3:35 AM by Stu
    Stu's avatar

    Perfect, helped me out loads. I had Windows 8.1 64 bit with 32 bit Office 365 and was bashing my head against the wall. Installing this and running the 32-bit SSMA (v6.0) worked nicely

    Cheers

leave a reply

 [Quick Submit with Ctrl+Enter]

Remember my details
Notify me of followup comments via e-mail