DAO External: Working with External Data Sources

  • Article

|  Overview |  How Do I |  FAQ |  Sample |  |  ODBC Driver List

This article explains the best approaches to using the MFC DAO classes with external data sources, primarily ODBC data sources.

Topics include:

  • External data source: definition

  • External data sources you can use

  • External data access choices

  • Performance considerations with external data

  • When you might need to open an external table directly

  • Other articles about accessing external data

  • For more information about accessing external data

External Data Source: Definition

Aside from working with a Microsoft Jet (.MDB) database on your local machine, you can use the MFC DAO classes to access external data of several kinds. External data includes data located in:

  • An ODBC data source, either local or on a network server.

  • An ISAM database such as dBASE® or Microsoft FoxPro®, accessible through the Microsoft Jet database engine, either locally or on a network server.

  • A Microsoft Jet (.MDB) database, created directly with Microsoft Access or created with DAO and stored either locally or on a network server, that contains tables you want to attach to a primary Microsoft Jet database.

External Data Sources You Can Use

The discussion in this and related articles applies to the following external data sources:

  • Microsoft FoxPro®, versions 2.0, 2.5, and 2.6. Can import and export data to and from version 3.0 but can’t create objects.

  • dBASE III®, dBASE IV®, and dBASE 5.0®.

  • Paradox™, versions 3.x, 4.x, and 5.x.

  • Databases using the Microsoft Jet database engine (Microsoft Access, Microsoft Visual Basic, and Microsoft Visual C++), versions 1.x, 2.x, and 3.0.

  • ODBC data sources, including but not limited to Microsoft SQL Server, SYBASE® SQL Server, and ORACLE® Server. An ODBC data source is any DBMS for which you have the appropriate ODBC driver. For Visual C++ versions 2.0 and later, you need 32-bit ODBC drivers (except on Win32s, where you need 16-bit ODBC drivers). See the article ODBC Driver List for a list of ODBC drivers included in this version of Visual C++ and for information about obtaining additional drivers.

  • Microsoft Excel version 3.0, 4.0, 5.0, and 7.0 worksheets.

  • Lotus® WKS, WK1, WK3, WK4 spreadsheets.

  • Text files.

External Data Access Choices

The MFC DAO classes give you two choices for accessing tables stored in external data sources. You can either:

  • Attach the tables to a Microsoft Jet (.MDB) database

    -or-

  • Open the external database directly.

Attaching Tables

When you attach a table, it is treated in most respects as if it were a Microsoft Jet database table in the current database — except that you can't modify the table's schema or open a table-type recordset on it. The connection information to the external data source is stored with the table definition, making it easy to open recordsets on the table. The data is still stored in the external data source, however. For information on attaching tables, see the article DAO External: Attaching External Tables.

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

Opening External Databases Directly

When you open a table directly, you specify the connection information each time you open the external database. This can involve communication overhead. For information on opening tables directly, see the article DAO External: Opening External Databases Directly.

Important   In most cases, attaching a table is a faster method for accessing external data than opening a table directly, especially when the table is in an ODBC data source. If possible, it's best to attach external tables rather than to open them directly. If you do open a table in an ODBC data source directly, keep in mind that performance will be significantly slower.

To attach or open a data source on a network, you must have access to the server and share and to the external table as well as appropriate permissions for access to the data, if applicable.

Performance Considerations with External Data

Keep in mind that external tables are not actually in your Microsoft Jet database. Each time you view data in an external table, your program must retrieve records from another file. This can take time, particularly if the table is an ODBC data source.

ODBC performance is optimal if you attach tables instead of opening them directly, and if you retrieve and view only the data you need. Restrict your queries to limit results and avoid excessive scrolling through records. For more performance tips, see the article DAO External: Improving Performance with External Data Sources.

For a discussion of why performance suffers with external data sources, particularly ODBC data sources, see the topic "Accessing External Databases with DAO" in DAO Help.

When You Might Need to Open an External Table Directly

Attaching external tables to a Microsoft Jet database is generally more efficient than opening the external data source directly. However, there still might be circumstances under which you would prefer to open the external database directly. They include the following:

  • Non-ODBC external data sources give faster performance if you open them directly. Only ODBC is slower when opened directly.

  • You need to enumerate the tables in the external data source to find out the database structure at run time. Unless you know the table names, you can’t attach them.

  • You need to manipulate the table’s structure. You can’t modify the schema of an attached table.

For more information, including procedures, see the following articles (in the recommended reading order):

For More Information About Accessing External Data

An additional source of information is the Advanced Topics book from the Microsoft Access Developer's Toolkit. You'll need to translate Microsoft Access Basic examples to MFC, but the chapter on Accessing External Data gives detailed advice on using external data sources such as Microsoft FoxPro, dBASE and Paradox.

For related information, see the topic "Accessing External Databases with DAO" in DAO Help.

For information about accessing specific external data sources, see the following topics in DAO Help:

  • Accessing Data in ODBC Databases with DAO

  • Accessing Data in a dBASE Database with DAO

  • Accessing Data in a Microsoft Excel Worksheet or Workbook with DAO

  • Accessing Data in a Microsoft FoxPro Database with DAO

  • Accessing Data in a Lotus Spreadsheet with DAO

  • Accessing Data in a Paradox Database with DAO

  • Accessing Data in a Text Document with DAO

  • Accessing Data on CD-ROM with DAO

See Also   DAO: Where Is...