DAO External: Attaching External Tables

This article explains how to attach a table from an external data source, such as an ODBC data source, to your current Microsoft Jet (.MDB) database. Attaching external tables is generally more efficient than opening them directly, as explained in the article DAO External: Working with External Data Sources.

Important For performance reasons, it is best to attach tables in ODBC data sources rather than to open them directly. You can open non-ODBC external data sources directly if you like.

Tip If you attach a table from within Microsoft Access, you can then use the table from MFC.

To attach an external table using the MFC DAO classes

Open your Microsoft Jet (.MDB) database (the one to which you'll attach the external table):
Construct a CDaoDatabase object, or obtain a pointer to one (from an open recordset object, for example) and call the object's Open member function.
Using the CDaoDatabase object, create a new CDaoTableDef object. Construct the tabledef object, then call its Create member function.
In the Create call, you can specify the source table name and the connect string. Or you can accept the defaults in Create and separately call SetConnect and SetSourceTableName to specify the connect string and the name of the table as it appears on the data source. The example following this procedure calls SetConnect and SetSourceTableName.
Attach the external table by appending it to the CDaoDatabase object's TableDefs collection.
Call the tabledef object's Append member function.
Use the attached table as if it were actually a table in the Microsoft Jet database.
You can do the following, among other things:
- Use the table to create a recordset.
- Examine fields and indexes in the table.
- Get or set validation conditions for the table.

The following example illustrates how to attach an external table:

// Construct the database and the tabledef
CDaoDatabase db;
db.Open("C:\\datatbase\\theDB.mdb");

// Construct the tabledef then create attached table
CDaoTableDef td( &db );
td.Create( "Preferred Customers", 0, "Customers",  
"ODBC;DSN=afx;UID=sa;PWD=Fred" );

// Attach the tabledef to the external data source
td.Append( );

// Use td ...

The parameters to create are the tabledef name, attributes, source table name, and connect string.

Your link to the attached table remains active unless you delete the tabledef object or move the source table. If you move the source table, you can refresh the link using the tabledef object's RefreshLink member function.

Note For the external indexed sequential access method (ISAM) databases, such as FoxPro and dBASE, specify the full path to the directory in which the database files are located when a database name is called for.

Tip Because a tabledef object name can be any valid Microsoft Access table name, you can give the attached table a more descriptive name than is often allowed in the external data source. For example, if you attach an external dBASE table named SLSDATA, you can rename the attached table as “Sales Data 1995 (from dBASE).” The code in the previous example illustrates this.