Migrating from Access 2000 to SQL Server 2000 - Exporting tables from MS Access 2000 to SQL 2000 Server

(Page 4 of 6 )

There are several options for exporting the tables from MS Access to SQL 2000 Server. One could use DTS (Data Transformation Service) to transfer all the tables in one go. You could also export tables to SQL Server using an ODBC connection. This may entail sending tables one at a time to the SQL Server. 

Establishing a Connection for this Database

In this tutorial, for example, each table was exported using an ODBC MS SQL Server setup whose details are as shown below. Only the final step of the connection steps is shown here, but you would start from the Control Panel ->Administrative Tools->ODBC Manager. You would use a SQL Server driver.  The System DSN created is called MigrateAccess as shown here.

After the connection is established, you could export all the tables to the SQL 2000 Server as shown. This is a montage of the database plus one of the tables showing the data. As soon as the data is exported, there is no need for the data to remain on the MS Access file. One could delete those tables. Now there are no tables on the Migrate1.mdb, but there are objects, queries, forms, reports, and so forth. At the moment they are orphaned.

Next: Establishing links to SQL 2000 Server database >>
 

More Microsoft Access Articles
More By Jayaram Krishnaswamy

Comments On: Migrating from Access 2000 to SQL Server 2000

· Database growth is a natural consequence of the business growth, and to meet all the...    · It looks like you are still using Access as a front-end in this scenario. Why not...    · I am not suggesting the best practice which may be, even not use Access at all. This...    · I went throught the conversion exercise using Access2003 and SQLServerExpress2005...    · Microsoft always supports for a while till the next version is in the horizon,...    · Access Project is in Data Access Pages is out in Office 2007.What do you say...   >>> Post your comment now!