Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
ACC2000: How to Use Data Transformation Services (DTS) to Export Data from a Microsoft Access Database to a SQL Server Database
Article ID: 250616 - View products that this article applies to.
This article was previously published under Q250616
Novice: Requires knowledge of the user interface on single-user computers.
This article applies only to a Microsoft Access database (.mdb).
For a Microsoft Access 2002 version of this article, see 285829
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).
NOTE: Using DTS to import Access tables creates the Unicode character fields nChar, nVarChar, and nText by default. These fields may cause problems when linking from Access 97, which does not support Unicode. Microsoft recommends that these fields be converted to Char, VarChar, and Text when linking from Access 97.
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:
Using the DTS Import and Export WizardThe 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.
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: