SSMA V4 and V5, Access2007->SS2008. Always fails on data migration. Never can connect to source DB. RRS feed

  • Question

  • Using Windows 7x64, 32-bit office. I do NOT have the Office2010 drivers installed, and would prefer not to do so, as I've tried them in the past and no matter what combination of 32/64-bit software I try, it always fails to find the driver.

    In any case, 2010 drivers should not be needed.

    I've tried  32-bit versions of SSMA, 4.2.2330 QFE1, and today tried 5.1.1105. In both versions, I can create a package. Specifically, I can select the Access and SQL databases, and can complete the Convert and Load steps (i.e. analyzing the database, creating the SQL database). No errors - a few warnings about various conversions of constraints. Clearly SSMA can at least read the Access database and its schema.

    The problem is that NONE of the 200 tables can have data migrated. In both cases, SSMA fails trying to connect to the Access database, which seems contraindicated by the fact that it must have connected in order to do prior steps. But perhaps those prior steps used a different mechanism that whatever SSMA uses to open data.

    Unfortunately, I also get this:

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

    As I say, I'm using 32-bit Office, 64-bit Windows. Well, we all know MS sucks at error messages, and this one is no exception. I am not running SSMA as a 64-bit process. The title bar says "[Running in 32-bit mode]". And the fact is, most of the time I don't get the message. Sometimes I do. This is obviously a long-standing bug, as many users report the problem, and it exists in the latest version 5. And once it starts happening, both version 4 and 5 will consistently report this error. When it doesn't happen, they seem to at least be able to do the schema work.


    But the above is not the main problem, surprisingly. In both version 4 and 5 of SSMA, it fails to connect to the Access tables for data copy, even when the above error has not been reported.

    In version 4, it simply says it failed to connect to the Access database. In version 5, it specifically says the Office12 drivers are "incorrectly installed", even though they aren't installed at all. Basically, V5 appears useless without Office2010 runtime, even though during its intall it warns that it will fall back to the older Access drivers if Office2010 runtime isn't installed. (Presumably we all also know that even though it reports "Office12" as the driver, it should be "Office14" - this is also a known bug for about a year now).

    I *can* do data import (not SSMA, but using import in SSMS), but SSMS import doesn't do anything to manage the table order, so I basically get one or two tables in, then hit a table with a referential rule that isn't satisfied because the parent table hasn't yet been imported. I'd have a serious mess on my hands with 200 tables, with many, many dozens (if not hundreds, actually) of relations.

    So basically, I want to use SSMA, but it simply fails, in every case, to connect to a single table.

    I'm guessing the error in both cases is that it is trying some connection string that simply isn't going to work, but I can't find out how to adjust that, or configure what it uses to connect. I hope there's some way.

    First then, is there any way to actually see what it is trying to do? Can the oledb driver or ODBC driver connection string be set or adjusted in any way?


    And finally, considering the relations and the necessity to import in a "correct" order, is there another tool that would do the data moving AND figure out that order? Years ago, ErWin would do this, but I no longer have access to that tool.



    Thursday, September 29, 2011 9:45 PM

All replies

  • Hi R.Berman,

    You may have a try to use a File DSN to connect to the SQL Server instance by Upsizing Wizard on Access 2007. For more information, please see the workaround on this KB article: http://support.microsoft.com/kb/838594.

    Also, Move Access data to a SQL Server database by using the Upsizing Wizard.

    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    Monday, October 3, 2011 10:55 AM
  • Hi Stephanie,

    I don't think the upsizer is an option. It was tried over a year ago and it has so many deficiencies in handling certain situations that it basically failed to even start translating this large, complex database. SSMA easily handled, without complaint, the many situations that the upsizer reported as fatal errors. In particular, field names which are legal in Access, but are reserved words in SQL, cause the upsizer to completely fail. Unfortunately, several of these are very common in this database and aren't realistically changed without pervasive changes to hundreds of queries, and much sql-building code in the Access application. 

    Of course, all that's needed in SQL Server is for these field names to be wrapped with "[]", but upsizer doesn't do that. SSMA does. And there were other problems.


    I think this very common problem with the COM library needs to be ADDRESSED. Telling us not to use this critical tool from Microsoft as a "handling", and to use an older, deficient tool instead, after SSMA has been around for years, and certainly 64-bit being the preferred OS base, while Access is only very recently 64-bit, is simply not acceptable. Where is the correction for this? Or the workaround that allows SSMA to work correctly in this kind of environment? Microsoft makes this SSMA specifically for Access, yet fails to work in one of the most common environment scenarios?



    Monday, October 3, 2011 4:36 PM
  • Do you have Microsoft.Office.Interop.Access.Dao.dll on the machine which is provided in office2007 and office2010.
    Wednesday, November 30, 2011 6:39 AM
  • Yes, office 2007 is fully installed. The problem is clearly with the install of the office 2010 connectivity components. since installing that, all these problems occur.

    I created a virtual machine with the same environment, but NEVER installed office 2010 connectivity, and on that machine, SSMA runs without this problem.

    I have researched and found many, many people are basically hosed after the 2010 connectivity install. Uninstalling them does not help at all - no change in the problem.


    Microsoft has fully dropped the ball on this one. 

    Wednesday, November 30, 2011 6:53 AM
  • could you check this link


    Tuesday, May 1, 2012 10:58 AM