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 :

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~
LVL 3
TusitalaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Theo FitchnerCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TusitalaAuthor 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~
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.