Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How To Export and Import Access Tables Using DAO or ODBC

How To Export and Import Access Tables Using DAO or ODBC

View products that this article applies to.

Summary

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

↑ Back to the top

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$]


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

↑ Back to the top

Keywords: KB200427, kbjet, kbhowto, kbdatabase

↑ Back to the top

Article Info
Article ID : 200427
Revision : 8
Created on : 8/11/2005
Published on : 8/11/2005
Exists online : False
Views : 519