SSMA Access BE Linked Table Migration Linking Issue

Tusitala used Ask the Experts™
Hey guys,

So, I have got myself into a mess with SSMA and need some help!

As a first time user of SSMA, I was toying with it earlier to test it out and decided to import three existing tables from an Access be db into a test SQL server db that I created for testing.  When asked if I want to link the migrated tables back to the Access be, I checked "Yes".  All worked fine, typical SQL warnings, no problem.

Then, after the migration of my three tables completed, I viewed them in SSMA and did not like the results in some of the data types that were converted.  So, like any novice would do, I decided to have another go and proceeded to delete the tables from SQL and the linked tables from Access.

Now, whenever I try to run the same migration steps for the same three tables, SSMA keeps showing the tables that I previously deleted as "Linked" per below image :

I tried using the Right Click -> "Unlink Table" command in SSMA but it fails each time with errors shown per below image :

I have also tried changing the destination SQL db, moving the tables to a new Access db, and for the life of me, I cannot seem to figure this out!

Any ideas on how I can move past this problem?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You didn't specify which MS Access data types that weren't converted properly. However, below is a solution to you two problems.

You need to create a new migration project in SSMA. SSMA usually renames the Access tables in the Access file by appending the string '$local' as a suffix. In effect, this modifies the Access file and it seems SSMA is still working with (or referencing) this modified Access file. It's not irreversible but the quickest solution is to run the migration anew. I assume you have a backup of the original MS Access file (mdb or accdb) before you used SSMA the first time. If yes, do the following:

1. Use SSMS to delete the newly created SQL Server database (the one SSMA migrated your Access tables and data to).
2. In SSMA, you need to specify data type conversions. Click on Tools and select Default Project Settings. Select the version of SQL Server that you are migrating to and click on Type Mapping in the left column. I have found that automatic/default works best. However, there are two Access data types that SSMA fails to convert properly. They are:

a. MS Access data type: 'Date/Time' MUST be converted to SQL Server data type: 'datetime'. Don't use anything else (not even 'datetime2') as MS Access won't understand it.

b. MS Access data type: 'Attachment' should be converted to SQL Server data type: 'varbinary (MAX).

You should be fine when you run the conversion and migration.


Hi Theo,

Thanks for responding to my question.

So, after reviewing your recommendations, I found that SSMA does a lot more than modify the source Access file itself.  It actually modifies the table definition in the source database and you were right, it is irreversible!

After running through the various methods you suggested, I actually found that the only way I could move beyond this without reverting to a back-up was to recreate each table and start the process fresh.  I had no issues reverting to a back-up but I wanted to further understand why I couldn't do what I wanted with SSMA.  

My conclusion is that unless you are absolutely sure you will not need to remigrate your tables using SSMA, don't ever check the "Link" when running the migration wizard as it will come back to bite you!

Again, thanks for your input it really helped me tease out the issues I was having.


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial