[AccessD] MDB to sql server

That has been my experience as well.  And do the upsize from the data
file, not the FE.

Charlotte Foust

-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett at ilc.gov.au] 
Sent: Tuesday, January 20, 2004 6:41 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [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.
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com