Importing Access tables into SQL Server

[Declan]

I tried importing Access tables from 3 different Access database .mdb files
using the upsizing wizard in Access 2003. When importing tables in the first
database, it imported some of the tables, and it got hung up when importing
one of the tables, so I had to end task, and that table's design got
imported, but none of the data got imported, and the remaining tables weren't
imported. The same thing happened with a different table in the next
database, and the third database ran to completion, giving me a summary
report at the end, but one of the tables didn't get imported. How can I find
out why these tables didn't get imported? Is there any way to import the
tables into SQL Server from SQL Server instead of exporting them into SQL
Server from Access? An older version of SQL Server had a feature called DTS.
Is this feature still available? I remember that DTS told you exactly what
records it had a problem importing instead of just hanging up or not
importing any of the records in a table.

 

[Sylvain Lafontaine]

Probably a foreign key problem between tables. Remove all the relationships
before making the upsize (you can re-etablish them later on SQL-Server) or
use another tool like SSMA or upsize only the schema of the tables and then
use heterogenous queries from an MDB file with linked tables (or making call
to the DBEngine with VBA) to transfert your data.

http://www.microsoft.com/sql/solutions/migration/access/default.mspx