How do I upgrade an Access database to SQL 2000 using DTS?

The following article details the steps you can take to convert your existing Access database to a SQL 2000 database using a component of Microsoft Enterprise Manager called DTS. You will need to have a copy of Enterprise Manager installed on your local computer. If you do not own a copy of Enterprise Manager you can download a 120 day trial version from Microsoft

To upgrade your Access database to a SQL 2000 database, please follow these steps:

1. Open SQL Enterprise Manager on your local computer and connect to your database. For information about connecting to your database please click here
2. From the Tools Menu, expand Data Transformation Services.
3. Click Import Data....
4. Click Next to begin the DTS Import/Export Wizard".
5. Enter the following information on the Choose a Data Source screen:
   • Data Source - Select 'Microsoft Access'.
   • File Name - enter the location of the Access database on your local computer.
   • Username and Password - only necessary if your Access database is password protected.
   
6. Click Next.
7. Enter the following information on the Choose a destination screen:
   • Destination - Select 'Microsoft OLE DB Provider for SQL server'.
   • Server - enter the address of your shared SQL webserver in the following format, servername.safesecureweb.com.
   • Select the Use SQL Server Authentication radio button.
   • Username and Password - enter the username and password of your shared SQL database.
   • Database - Select your shared SQL database name from the drop down.
   
8. Click Next.
9. Select Copy table(s) and view(s) from the source database. This should be the default selection.
10. Click Next.
11. Select the table(s) you wish to import. Click Select All to copy the entire database.
12. Click Next.
13. Click Finish.

The selected tables of your Access database should now be uploaded into your shared SQL 2000 database.