ACC2000: How to Use Data Transformation Services (DTS) to Export Data from a Microsoft Access Database to a SQL Server Database

ID: Q250616

The information in this article applies to:
  • Microsoft Access 2000
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).


SUMMARY

Data Transformation Services (DTS) is an alternative method that you can use to move data from a Microsoft Access database to Microsoft SQL Server or Microsoft Data Engine (MSDE).


MORE INFORMATION

DTS provides the functionality to import and export data between SQL Server and any OLE DB or ODBC data source, including Microsoft Access. Both SQL Server and MSDE include DTS and the DTS Import and Export Wizard that enable you to create and run DTS packages interactively.

You can use the DTS Import and Export Wizard to automatically create tables on SQL Server, and then to copy data from Access to the new SQL Server or MSDE tables. DTS can move data at a faster rate than the Microsoft Access Upsizing Wizard can, but DTS does not provide all of the features of the Access Upsizing Wizard. The following is a list of actions that the Access Upsizing Wizard can perform, but which the DTS Import and Export Wizard cannot perform:

  • If an Access table has a primary key, the Access Upsizing Wizard automatically re-creates the primary key on the table that it generates on SQL Server; DTS does not.


  • The Access Upsizing Wizard automatically migrates all rules and defaults that exist in a table to SQL Server; DTS does not.


  • If tables in an Access database are related, the Upsizing Wizard automatically re-creates these relationships on SQL Server; DTS does not.


  • DTS does not upsize any queries that exist in an Access database.


Using the DTS Import and Export Wizard

The following steps demonstrate how to use the DTS Import and Export Wizard to copy the Orders and Order Details tables from the sample database Northwind.mdb into a new SQL Server or MSDE database.

NOTE: Follow these steps on the computer that is running SQL Server or MSDE.

  1. Click Start, point to Programs, and then click either the MSDE or Microsoft SQL Server 7.0 program group, depending on which server you are running.


  2. In the MSDE or Microsoft SQL Server 7.0 program group, click Import and Export Data.


  3. When the Data Transformation Services Wizard appears, click Next.


  4. In the Choose a Data Source screen, pick Microsoft Access in the Source box.


  5. Click the Build button (...) next to File Name box, and browse to the Program Files\Microsoft Office\Office\Samples folder.


  6. Double-click Northwind, and then click Next.


  7. In the Choose a Destination screen, choose Microsoft OLE DB Provider for SQL Server in the Destination box.


  8. In the Server box, click (local).


  9. Enter a user name and password for a SQL Server or MSDE user who has permissions to create tables and databases on the server.


  10. Click Refresh.


  11. In the Database box, click <new>.


  12. Provide a name for the new database in the Name box. Accept the default values in the Data file size and Log file size boxes, and then click OK.


  13. Click Next twice.


  14. In the Tables list, click the Orders and Order Details tables.


  15. Click Next twice, and then click Finish.


  16. The DTS package that you created with the DTS Import and Export Wizard will run. When the DTS package is finished, click OK, and then click Done to close the wizard.



REFERENCES

For more information about DTS and the DTS Import and Export Wizard, refer to SQL Server 7.0 Books Online, which is available for download from the following Microsoft Web site:

http://support.microsoft.com/download/support/mslfiles/sqlbol.exe

Additional query words:

Keywords : kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: January 24, 2000
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.