[dba-SQLServer] importing Access data

Hi Susan:

I am going through a similar event. The only difference is the source
database is a legacy DB. I puzzled over what would be the best method and
decided on writing a full set of queries and DTS import routines which will
extract and manipulate the data out of the existing database and into the MS
SQL. Most is done from the SQL end.

When the testing is completed I will move the client over in an evening and
they will run in parallel for a month and then drop the old system. (The FE
is also going to be new; starting from a DOS based DB named Clarion to
ASP.Net) 

To make a long story short, build, test and automate the import routines,
using the backup data and then run the data import yourself. This is because
if any data in the old system is wrong... missing keys, missing records and
has records with bad-data... (even since your latest backup test data) the
import will crash with no one to help. 

HTH
Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Susan
Harkins
Sent: Friday, August 19, 2005 9:23 AM
To: SQLList
Subject: [dba-SQLServer] importing Access data

I've got an existing SQL Server database and an Access database. The client
wants to dump the Access data into the SQL Sever database and dump the .mdb
file completely. This is a one-time job. 
 
I figure I have two approaches:
 
1. We can clear a block of time to manually import the Access data. I'd be
working with a backup copy and they'd have to agree not to use the SQL
Server database while I was importing -- 
2. After reviewing the backup, I could write the necessary stored procedures
and let them run them to import the data themselves. That way, they wouldn't
have any downtime. 
 
Any comments? Any other suggestions? I don't have to convert anything other
that Access datatypes that won't import as is -- any comments about that? I
don't anticipate much trouble outside of maybe having to dump some dates,
but I haven't reviewed the Access data yet, so that might not be necessary. 
 
Susan H. 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com