SQL Server Migration Assistant

"Microsoft SQL Server Migration Assistant (SSMA) for Access is a tool for migrating databases from Microsoft Access 97 through Access 2003 to Microsoft SQL Server 2005. SSMA for Access converts Access database objects to SQL Server database objects, loads those objects into SQL Server, and then migrates data from Access to SQL Server." (from the readme file)

This is an extremely useful tool, the SSMA does a very nice job of quickly upsizing an Access 97 - 2003 database to SQL Server, creating the tables and relationships & migrating the data, with just a few clicks.

It renames the existing Access files, for example tblCities becomes SSMA$tblCities$local so your original data is preserved until you're satisfied that the upsize worked as expected, and the linked tables assume the names of the original tables e.g. tblCities, omitting the "dbo_" prefix that you normally see when linking to MSSQL tables.

Note that you must have a primary key defined for every table, otherwise it only grants SELECT permissions on those un-keyed tables.

The only possible downside is the way that auto-numbering works - in Access, an Autonumber value exists as soon as you begin to create a new record, but in SQL Server an IDENTITY value does not exist until the new record is saved; if your Access app depends on that behavior, then you'll need some re-write in your coding.

Oh yes, you need to install the J# 2.0 redistributable package prior to installing SSMA but the installer alerts you and provides a quick link to download & install it.

There is also a CTP available for migrating the new Access 2007 to MSSQL 2005, and another CTP for migrating to MSSQL 2008 that also supports Access 2007.