Accessing Data with DAO
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.
Accessing Data with RDO
RDO is a popular way to build efficient data access to relational ODBC databases. With RDO, you can create simple cursorless result sets, or more complex batch or client-side cursors. You can limit the number of rows returned and monitor all of the messages and errors generated by the remote data source without compromising the executing query.
One of the important features in RDO that is not available in DAO is the ability to handle queries and stored procedures that return multiple result sets. This feature is commonly used whenever you need to load multiple controls (such as combo boxes) with data from multiple tables. This feature eliminates the redundant processing and network traffic overhead of using many separate queries.
Note Because the object models are very similar, you can take your existing applications that use DAO data access and easily convert them to use RDO. For more information on migrating from DAO to RDO, search for "RDO Compared to Microsoft Jet/DAO" in MSDN Library Visual Studio 6.0.
Most RDO methods can be executed either synchronously, asynchronously, or through the use of event procedures to notify your code when operations are complete or about to be executed. Using the asynchronous operations and event procedures, your application can do other work while lengthy queries are executed.
While RDO and any specific ODBC driver can leverage unique data source functions by directly exposing the ODBC API functions, the same functions might not be supported by other drivers. If your application is designed to be used across a variety of databases, these direct functions should be used carefully or not at all.
RDO has the ability to dissociate and reassociate an rdoQuery object from its connection. This is especially useful because you can associate the query with other connections, and thus apply the same query across multiple data sources without re-creating the individual connections.
RDO (like ADO) also includes a client batch cursor library that supports optimistic batch updates. With batch updates, you can create a result set, modify the data as required, and subsequently make all of the changes using the batch update method. This improves performance by reducing server, network, and ODBC overhead.
An RDO-based application uses the following operations to access a data source.
- Set the environment handle Identifies the memory location for global data and status information for the defined connections.
- Open the Connection Specifies the connection string with information such as data source name, user identification, password, default database, network name of the data source server, and name of the data source driver.
- Open the result set This runs a query and creates a result set.
- Use the result set The result set is now available to your application. Depending on the cursor type, you can browse and change the item data at either the server or client side.
- Close the connection Drops the connection to the data source.
- Free the environment handle Drops the global data and frees all associated memory.
With RDO you can create database-independent code that automatically adapts to a variety of ODBC databases.