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