Migration of an Access Database. How is the best way?

[John Sheppard]

Hello there,

I am migrating an Access database to Sql Server. There are many many
problems with the access database (Schema and data) that need to be fixed in
the process.

What I was initially doing is writing a script which changes the schema and
data as I go. I then planned to run this script when we go live with the SQL
Server version. After working on this for a month or so I'm realising that
perhaps this isnt going to work. There are just too many changes and
scripting everything takes ages compared with just writing a new schema.

What I was thinking would be better would be to create a brand new schema
and just write a script to import the old data.

What do most people normally do?

Any advice would be very much appreciated
John Sheppard

[Plamen Ratchev]

I have normally done it this way:

1). Import all Access table to staging tables in SQL Server
2). Create the correct schema
3). Write scripts to transfer data from staging tables to the new schema

Creating new schema in SQL Server is a good idea because SQL Server has more
superior support for constraints and referential integrity. Also, it is best
to define the correct data types as straight conversion from Access results
in many columns defined as NVARCHAR(255).

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[John Sheppard]

"Plamen Ratchev" <Pl****@SQLStud io.comwrote in message
news:hc******** *************** *******@speakea sy.net...

>I have normally done it this way:

1). Import all Access table to staging tables in SQL Server
2). Create the correct schema
3). Write scripts to transfer data from staging tables to the new schema

Creating new schema in SQL Server is a good idea because SQL Server has
more superior support for constraints and referential integrity. Also, it
is best to define the correct data types as straight conversion from
Access results in many columns defined as NVARCHAR(255).

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Yes I think you are right, this seams like the best way to do things. I wish
I had of thought of this and gone this path to begin with.

Thank you
John sheppard