Avatar of Tusitala
 asked on

SSMA Access BE Linked Table Migration Linking Issue

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?

Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon
Theo Fitchner

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

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.

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy