HOWTO: Export and Import Access Tables Using DAO or ODBC

ID: Q200427

The information in this article applies to:

Microsoft Data Access Components versions 2.0, 2.1 SP2
Microsoft Visual Studio versions 6.0, 6.0 SP3

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 <Target> FROM <Source>

Where <Source> is the table we want to copy and <Target> is the destination for the table. Note that this SQL statement will attempt to create <Target> with the same table structure as <Source> and populate <Target> with all of the records from <Source>.

Note that <Target> and <Source> allow the following three variations:


   [<Full path to Microsoft Access database>].[<Table Name>]
   [ODBC;<ODBC Connection String>].[<Table Name>]
   [<ISAM Name>;<ISAM Connection String>].[<Table Name>]

Here are some valid syntax examples:


[c:\mydata\db1.mdb].[Customers]
   [ODBC;DSN=MyODBCDSN;UID=sa;PWD=;].[authors]
   [ODBC;Driver=SQL Server;Server=XXX;Database=Pubs;UID=sa;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:


   #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=sa;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:


   #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=sa;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();
   }

Additional query words:

Keywords : kbDatabase kbJET kbMDAC kbODBC kbVS600 kbGrpMDAC kbDSupport kbADO210sp2 kbGrpODBC
Version : WINDOWS:2.0,2.1 SP2,6.0,6.0 SP3
Platform : WINDOWS
Issue type : kbhowto