[thelist] Migrating from Access to MS SQL Server

Bob Davis bobd at members.evolt.org
Thu Nov 2 07:17:13 CST 2000

on 11/2/00 7:19 AM, Nick Koleszar at nick at aussieinlondon.com wrote:

> I have to migrate a fairly simple application from MS Access to SQL Server
> 7. At the moment, all sql queries are performed in CFM pages so there are no
> Access queries to migrate, just 6 tables.
> I am looking for the best book to guide me through migration and best use of
> SQL Server 7.
> I have found two books but can anyone give me their reviews on these or
> others?

Last time I had to do it I got the Migrating from Access to SQL7 white
papers from MS.  They do a pretty good job of explaining it, but they don't
tell you all of the pitfalls.

Here's what I did to make the database actually work as I wanted it.

1. Use the DTS stuff to create a shell database.  Basically, all I want here
are the names and structures.

2. Go in by hand and modify the data types of all the columns in all of the
tables. DTS has a pathological fear of VARCHAR from what I can tell, and
doesn't really know what to do with the MEMO data type in Access.   All MEMO
fields were changed to TEXT(16) for some reason, and all of the info in
these fields was truncated.

3.  Delete all info from the new SQL7 database.

4. Go back to access and link the tables from the SQL7 database into the
Access database.

5. Write a few append queries to move all the data from the Access tables to
the imported SQL7 tables.

This is the only way I could figure to get the data moved reliably.

It isn't as bad as it sounds.



bob davis
bobd at members.evolt.org

More information about the thelist mailing list