Receiving error when using "SQL Server Migration Wizard Assistant for Access" to import MDB files into SQL Server.

I am using the "SQL Server migration Wizard Assistant" provided by Microsoft to import about 8 Access databases into SQL Server 2005. When I select the database to migrate and it loads into the program it shows an errors stating it could not load the content of the MDB files. (Tables and Queries dont show up) Here is a screenshot of the error I receive.

http://www.filtrationproducts.com/images/AccessMigrationError.jpg
LVL 1
filtrationproductsAsked:
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.

tonydemarcoCommented:
Try it the other way around.
Assuming that you have ODBC connection to SQL database.
If not create one. Then;
Highlight the Table.
From Access "File" "Export" (Save As Type dropdown) "ODBC-Databases()" -
Export Your Table Name to ODBC DSN.

Hope this helps!
0
filtrationproductsAuthor Commented:
So your saying to just export the ODBC linked table in the access file before doing the migration wizard so its not an issue? Then just import it into SQL when the migration is complete?
0
tonydemarcoCommented:
For clarity,
You have a table in MS Access that you want to export to MS SQL.
You have an ODBC connection to the database in MS SQL.
In one opeation from MS Access you can "Export" the table directly to MS SQL using the instructions above. Simple one step.

Also, make sure that the tables you are exporting from Access have an indexed field that has
"no duplicates". You can create one with "autonumber" if need be.
ie. field name "idx" "autonumber" "indexed (Yes No Duplicates)"
Some tables will export without an indexed field but you should get in the habit of doing so on all data tables.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

filtrationproductsAuthor Commented:
To export these to SQL from Access manually for every table/query/marco/report/form in all 6 access databases would take forever. That is why I want to use the Migration Wizard.

When I cancel the wizard and just use the "add database" button they load fine. But when I try to use the actual Wizard they always error out with that message... no matter how simple the database is. I want to use the Wizard because it will automatically link MDB databases to the SQL server so I can use the MDB as the front end program to access/modify the back end SQL data.
0
filtrationproductsAuthor Commented:
I tried adding all the databases into the migration assistant program manually then saved the project. I then received an error message that said "metadata missing".
0
tonydemarcoCommented:
Started with:
"I am using the "SQL Server migration Wizard Assistant" provided by Microsoft to import about 8 Access databases into SQL Server 2005"

Now:
"I want to use the Wizard because it will automatically link MDB databases to the SQL server so I can use the MDB as the front end program to access/modify the back end SQL data."

Circular logic. You link the SQL to the Front End MS Access via ODBC, not the other way around.

"To export these to SQL from Access manually for every table/query/marco/report/form in all 6 access databases would take forever."
You can't export queries, macros, reports or forms. You can only import tables.
MS SQL is only a container for data, not an application in itself.
You must use MS Access as the front end program. Access uses the data that is housed in SQL to create the queries, forms and reports.

You are on the right track if you want to migrate tables from Access to SQL and then create reports etc.
As the database grows, SQL is a better container for data than Access and it is safer to keep your data seperate from the front end and also for front end portability and distribution.

Hope this helps.
0
filtrationproductsAuthor Commented:
Tony,
I tried doing this same practive on our live version of SQL Server and everything migrated and linked flawlessly. The copy of SQL I was using was a test copy I installed on my laptop so I wouldnt have to worry about interupting or damaging the live copy of SQL's database. But everything seems to have worked fine.

The copy I installed on the laptop was a trial version of SQL that was provided with the "SQL Server Installation and Maintanance" training kit written by Microsoft. For some reason this one did not work with the migration assistant application.

Who knows?? But thanks for your help!
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
tonydemarcoCommented:
cheers
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.