We help IT Professionals succeed at work.

SSMA Access BE Linked Table Migration Linking Issue

Tusitala
Tusitala asked
on
209 Views
Last Modified: 2017-04-03
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 :

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

errors1
errors2
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?

~Tala~
Comment
Watch Question

This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
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.

~Tala~