[AccessD] MDB to sql server

I've found its better in this case to use the upsizing wizard from within
Access 2k, as it seems to handle the native data types of Access better than
importing from DTS.

Make sure you have the Access patch installed first (can't remember what its
called but its available at Office Update - something like a 'readiness
update' or the like).

Still check your primary key and identity fields in SQL afterwards as I
can't recall if there were any other issues..  There may well have been a
couple of options you needed to change during the export process to retain
all constraints.

Cheers,
Andrew 

-----Original Message-----
From: John W. Colby [mailto:jwcolby at colbyconsulting.com] 
Sent: Wednesday, 21 January 2004 12:14 PM
To: AccessD
Subject: [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


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

IMPORTANT - PLEASE READ ******************** 
This email and any files transmitted with it are confidential and may 
contain information protected by law from disclosure. 
If you have received this message in error, please notify the sender 
immediately and delete this email from your system. 
No warranty is given that this email or files, if attached to this 
email, are free from computer viruses or other defects. They 
are provided on the basis the user assumes all responsibility for 
loss, damage or consequence resulting directly or indirectly from 
their use, whether caused by the negligence of the sender or not.