From Microsoft Access 2000 to Microsoft SQL Server

Migrating from Access to SQL Server

[Mechaworx]

Hi All,

I'm in the middle of creating a web site with a rather large database back-end. Unfortunately, when I started to create this site, I was using Microsoft Access 2000. Being new to Asp development, I have soon learned that using Microsoft Access as a format to store records may not be the way to go since I anticipate my database to get rather large rather quickly and I also anticipate a lot of online traffic.

It was suggested that I perhaps change to Microsoft SQL server. I guess my question is, how difficult would it be to do this process especially since I have over 600 records in my Access database so far and I anticipate this to triple within a year.

Thanks for any information
Gerry

[txbakers]

It's very easy to migrate from Access to SQL Server.

There are scripts that Access can export to run in SQL Server, and the data can be transferred throug ODBC I believe.

If not, you can export the data from access into a CSV file and import into SQL.

[skipfactor]

In Access 2002 at least: "Tools", Database Utilities", "Upsizing Wizard" will also do it.

[Mechaworx]

Thanks,
That's good to know.
Gerry

[davemarks]

Bear in mind though that you will probably have to edit your code.

For example access expects a date as #03/09/2003# while sql expects '03/09/2003'

Also in my experience, the upsizing wizard is not perfect. You normally have to change all your autonumbers, as they are made into ints without the identity set