Access tables show as linked tables in SSMA and do not get migrated

Question

Hello, I have been trying to migrate an Access DB (.accdb) to SQL server 2008 and things are failing for various reasons. In one of these migration attempts tables were migrated and SSMA created on the Access side a bunch of SSMA$...$local tables, which seem to be some kind of indirection link tables. However, because the access db was not fully migrated, we decided to undo the operation by converting to local table every linked table. We completed that step and then deleted all the SSMA$...$local link tables and finally we fixed a series of queries where the SSMA$...$local tables had been replaced by the original tables, reverting these to their original expression.

So far, so good, the original access db works and we have now attempted to address some of the original problems that prevented a successful full migration. So, now we are ready to do new attempts of migration, however, when I bring up the SSMA tool, once I add the AccessDB, the Access Metadata Explorer, reports all my "local tables" as "link tables" and refuses to migrate them.

If I try to unlink any of the link tables, I get log messages like the following:

Starting Phase #0
Processing table '[SAMPLETABLE]' in file 'MyAccessDB'... ...
Rolling back the table SAMPLETABLE from the database MyAccessDB...
Table 'SSMA$SAMPLETABLE$local' was not found in the Access database.
Operation failed.

 Errors: Table 'SSMA$SAMPLETABLE$local' was not found in the Access database.
Rollback operation complete.

Based on the above, it seems that my revert operation was not complete and although the Access db works, there was some state left in there by SSMA which causes the tables to still be perceived as linked.

I have tried several blind techniques to clean that leftover state, like saving the .accdb to another format like .mdb, but the bad state is copied along to the new table. If at all possible I want to avoid going to an old backup because this database is different enough from the starting point of our migration efforts.

So, here is my question: Is there a way, without resorting to a backup, that I can clean up that leftover state such that I can retry migrations?

Thanks

Answer 1

Hi Coffee_fan,

Before doing migration the best practice is to make a full back-up of you current database. I would like to suggest restoring the latest one and retrying migration by using SSMA or import and export wizard in SQL Server.

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 2

DId you ever figure this out?  I am having almost the exact same issue except without the errors.  Now when I try to migrate SSMA only migrates the newest tables