Accessing Data with DAO

  • Article

   

There are three categories of databases accessible through DAO and the Microsoft Jet engine, as described in the following list.

  • Native Microsoft Jet databases   These database files use the same format as Microsoft Access databases. These databases are created and manipulated directly by the Microsoft Jet engine and provide maximum flexibility and speed.

  • External databases   These are Indexed Sequential Access Method (ISAM) databases in several popular formats, including Btrieve, dBASE III, dBASE IV, Microsoft FoxPro versions 2.0 and 2.5, and Paradox versions 3.x and 4.0. You can create or manipulate all of these database formats in Visual Basic. You can also access text file databases and Microsoft Excel or Lotus 1-2-3 worksheets.

    Tip   The ODBC API is the interface that the Microsoft Jet database engine uses to access external non-ISAM databases. Although ISAM ODBC drivers exist, Microsoft Jet uses its own installable drivers.

  • ODBC Databases   These include relational databases that conform to the ODBC standard, such as Microsoft SQL Server.

The Microsoft Jet database engine translates operations on DAO objects into physical operations on the database files themselves, handling all the mechanics of interfacing with the many different supported databases.

A DAO-based application uses the following operations to access a data source:

  • Create the workspace   Defines the user session, including user identification, password, and database type (such as Microsoft Jet or ODBC).
  • Open the database   Specifies a connection string for a particular Workspace object, with information such as data source name and database table.
  • Open the recordset   Runs an SQL query (with or without parameters) and populates the recordset.
  • Use the recordset   The query result set is now available to your application. Depending on the cursor type, you can browse and change the row data.
  • Close the recordset   Drops the query results and closes the recordset.
  • Close the database   Closes the database and releases the connection.

With DAO you can work directly with ISAM tables and indexes. This was an early advantage to using the DAO data access model, but ADO with OLE DB providers can also provide the same functionality.

You can use DAO to perform DDL (Data Definition Language) operations that affect the structure of your database. For example, you can create, delete, and modify the table definitions.

As an older data access technology, DAO is limited to data stores that can be handled by the Microsoft Jet engine. If your application requires access to other types of data stores, DAO cannot provide it. Additionally, DAO cannot build queries using server-side cursors. Using DAO inflicts a big performance penalty because it uses the Microsoft Jet database engine.