[AccessD] MDB to sql server

My client DIS has finally ordered a new server for their database, a shiny
new Dell dual P4 2.4g with 1gb ram etc., Windows 2K Server OS and SQL Server
2K.

Given that the MDB was running on an old PIII 400 mhz NT running all the
email and everything else the company owned, just the new server hardware
should provide a decent boost in performance.  However they also want to
move to SQL Server.

I have a server machine here at the office running Win2K Pro and SQL Server
2K.  My intention is to do the data port from MDB to SQL Server at my office
in advance, figuring out how to do it and discovering all the pitfalls
before hand.

The database consists of approximately 90 tables, of which ~30 are lookup
tables, ~30 are main tables / child tables, and a handful of many-many.
This MDB is heavily normalized with relationships established, no cascade
delete.  I use Autonumber PKs exclusively.

I tried to do the import from inside SQL Server using DTS(?) but ended up
with 17 tables not imported, and the structure but not the data for several
main tables.  What is the best method to use for getting the database moved
into SQL Server, preferable maintaining the incrementing autonumber
methodology as well as the relationships, with referential integrity
enforced, no cascade deletes?

I went through this exercise a couple of years ago with a similar size
database, and managed, in the end to get it imported and functioning.

John W. Colby
www.ColbyConsulting.com