Microsoft KB Archive/200427

= How To Export and Import Access Tables Using DAO or ODBC =

Article ID: 200427

Article Last Modified on 8/11/2005

-

APPLIES TO


 * Microsoft Data Access Components 2.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Visual Studio 6.0 Enterprise Edition

-



This article was previously published under Q200427



SUMMARY
This article details how to import and export tables using SQL DML (data manipulation language) statements with the Microsoft Access ODBC driver.



MORE INFORMATION
The Microsoft Access ODBC driver can read and write data into Jet ISAM drivers as well as ODBC drivers using SQL statements. The driver understands a special SQL syntax that permits you to prefix a table name with any valid Jet connection string, which allows you to create SQL statements that move tables from one data source to another.

The basic concept behind the examples shown in this article is to open a Microsoft Access database and use the "SELECT ... INTO ... FROM" SQL statement. Internally, Jet performs the translations required for exporting or importing the data from the source and target data sources.

The general syntax of the "copy table" SQL is:

SELECT * INTO  FROM  Where  is the table we want to copy and  is the destination for the table. Note that this SQL statement will attempt to create  with the same table structure as  and populate  with all of the records from .

Note that  and  allow the following three variations:

  [].[] [ODBC;<ODBC Connection String>].[<Table Name>] [<ISAM Name>;<ISAM Connection String>].[<Table Name>] Here are some valid syntax examples:

Note You must change the uid= value and the pwd= value to the correct values before you run this code. Make sure that uid has the appropriate permissions to perform this operation on the database.

[c:\mydata\db1.mdb].[Customers] [ODBC;DSN=MyODBCDSN;UID= ;PWD= ;].[authors] [ODBC;Driver=SQL Server;Server=XXX;Database=Pubs;UID= ;PWD= ;].[authors] [Excel 5.0;HDR=Yes;DATABASE=c:\book1.xls;].[Sheet1$] For more information on creating valid Jet connection strings, see the following whitepaper:

Setting Connection String Parameters in DAO

The following code examples assume we have a copy of the Microsoft Access sample database Northwind.mdb named "C:\Nw97.mdb":

Importing and Exporting Data Using The CDaoDatabase Class
The following code example demonstrates how to copy the Customers table in NorthWind (named C:\Nw97.mdb) to Sheet1 of an Excel workbook named C:\Customers.xls using the CDaoDatabase method:

#include <afxdao.h> // Needed for MFC DAO classes. CDaoDatabase db; CString SQL; SQL = "SELECT * INTO " "[Excel 8.0;HDR=Yes;DATABASE=c:\\customers.xls].[Sheet1] " "FROM [Customers]"; try {     // Open database and execute SQL statement to copy data. db.Open( "c:\\nw97.mdb" ); db.Execute( SQL, dbFailOnError ); }  catch( CDaoException * pEX ) {     // Display errors. AfxMessageBox( pEX->m_pErrorInfo->m_strDescription ); pEX->Delete; }

The following example demonstrates how to copy the authors table from a SQL Server pubs database into NorthWind:

Note You must change the uid= value and the pwd= value to the correct values before you run this code. Make sure that uid has the appropriate permissions to perform this operation on the database.

#include <afxdao.h> // Needed for MFC DAO classes. CDaoDatabase db; CString SQL; // Change XXX to the name of your SQL Server. SQL = "SELECT * INTO " "[LocalAuthors] " "FROM " "[ODBC;Driver=SQL Server;SERVER=XXX;DATABASE=Pubs;UID= ;PWD= ;]." "[authors]"; try {     // Open database and execute SQL statement to copy data. db.Open( "c:\\nw97.mdb" ); db.Execute( SQL, dbFailOnError ); }  catch( CDaoException * pEX ) {     // Display errors. AfxMessageBox( pEX->m_pErrorInfo->m_strDescription ); pEX->Delete; }

Importing and Exporting Data Using The CDatabase Class
The following code example demonstrates how to copy the Customers table in NorthWind (our copy named C:\Nw97.mdb) to Sheet1 of an Excel workbook named C:\Customers.xls using the CDaoDatabase class:

#include <afxdao.h> // Needed for MFC DAO classes. CDaoDatabase db; CString SQL; SQL = "SELECT * INTO " "[Excel 8.0;HDR=Yes;DATABASE=c:\\customers.xls].[Sheet1] " "FROM [Customers]"; try {        // Open database and execute SQL statement to copy data. db.Open( "c:\\nw97.mdb" ); db.Execute( SQL, dbFailOnError ); }     catch( CDaoException * pEX ) {        // Display errors. AfxMessageBox( pEX->m_pErrorInfo->m_strDescription ); pEX->Delete; }

The following example demonstrates how to copy the Shippers table from NorthWind to a SQL Server table named RemoteShippers in pubs:

Note You must change the uid= value and the pwd= value to the correct values before you run this code. Make sure that uid has the appropriate permissions to perform this operation on the database.

#include <afxdb.h> // Needed for MFC ODBC classes. CDatabase db; CString SQL; // Change XXX to the name of your SQL Server. SQL = "SELECT * INTO " "[ODBC;Driver=SQL Server;SERVER=XXX;DATABASE=Pubs;UID= ;PWD= ;]." "[RemoteShippers] " "FROM [Shippers]"; try {     // Open database and execute SQL statement to copy data. db.OpenEx( "Driver=Microsoft Access Driver (*.mdb);"                "DBQ=c:\\nw97.mdb;", CDatabase::noOdbcDialog ); db.ExecuteSQL( SQL ); }  catch( CDBException* pEX ) {     // Display errors. AfxMessageBox( pEX->m_strError ); pEX->Delete; }

Keywords: kbhowto kbdatabase kbjet KB200427

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.