One way to convert Access database to Sql Server database is through Access’ built-in Upsizing Wizard, accessible through Tools -> Database Utilities -> Upsizing Wizard. It works both with Sql Server 2000 and Sql Server 2005.

When upsizing, you are provided with basically 3 choices: converting Access database to a ADP project; converting all tables via Access linked tables, or simply creating a Sql Server database and do nothing.

Choice 1 is probably most desirable but rarely works if you have a lot of forms in your Access application, especially if form names have spaces in them. You may have to recreate all or most of the forms in order to work. This is desirable because when it works, most of the processing will be done on Sql Server, which should be more efficient than Access’ Jet Engine.

Choice 2 is probably the most practical approach initially, as the wizard will create linked tables to Sql Server tables, rename all original Access tables with _local attached to their names. All forms should work as is.

You will also face a choice of using DRI and triggers. If you do not know what DRI is or are not sure about it, use DRI. DRI stands for Declarative Referential Integrity.

When the wizard finishes, you will be presented with a report. Pay special attention to table(s) skipped. Most likely that’s due to data cast error, caused by date data type differences between Access and Sql Server. Access Date field take values from 1 Jan 100 to 31 Dec 9999, whereas SQL Server datetime field accepts dates from 1 Jan 1753 to 31 Dec 9999.

To find out which column in Access caused the problem, you can try running Import data wizard within Sql Server Management Studio on those skipped tables. Below is a typical error message when data import from Access to Sql Server fails.

– Copying to [MySqlServerDb].[dbo].[MyTable] (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80004005 Description: “Invalid character value for cast specification”.
(SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task: There was an error with input column “MyProblemColumn” (144) on input “Destination Input” (101). The column status returned was: “Conversion failed because the data value overflowed the specified type.”.
(SQL Server Import and Export Wizard)

From the message above, you can see that MyProblemColumn has the data that caused the cast error. Open up Access, sort value on MyProblemColumn, clean up the invalid data, and you can try again.

I recommend that you make a production copy of your Access database, and run it against Sql Server in test or development environment. If it fails, just blow away the database created in Sql Server, fix the problem in Access, and try again, until you get all kinks worked out.