KB200427 - How To Export and Import Access Tables Using DAO or ODBC

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:

Note You must change the uid=<username> value and the pwd=<strong password> 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=<username>;PWD=<strong password>;].[authors]
   [ODBC;Driver=SQL Server;Server=XXX;Database=Pubs;UID=<username>;PWD=<strong password>;].[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=<username> value and the pwd=<strong password> 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=<username>;PWD=<strong password>;]."
         "[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=<username> value and the pwd=<strong password> 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=<username>;PWD=<strong password>;]."
         "[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();
   }

How To Export and Import Access Tables Using DAO or ODBC

SUMMARY

MORE INFORMATION

Importing and Exporting Data Using The CDaoDatabase Class

Importing and Exporting Data Using The CDatabase Class