Share via

Visual Basic Concepts

  • Article

Building DAO Cursors with Jet

Whenever you execute a query that returns rows, you must decide which type of cursor is to be created to manage the result set. Cursors are simply a set of rows returned from the data source that meet the criteria you specify in a SQL statement. The SQL statement indicates which table(s) and column(s) are to be returned and limits the rows based on criteria in a WHERE clause.

When using DAO and Jet to fetch information from the database, you must use the Recordset object to build a cursor. This can be accomplished using attached tables, QueryDef objects with Connect strings and SQL queries, or a combination of the two.

The following terms are used when describing a cursor:

  • Scrolling is the ability to position to a specific row of the result set using one or more DAO methods.

  • Updatability indicates whether a cursor permits changes to the underlying database rows.

  • Membership in the result set is determined by the SQL query. As you use the AddNew or Delete methods to add or remove rows or as others sharing the database make changes, the membership of a cursor might or might not reflect those changes.

  • Static data values in static cursors have been copied to local memory where the cursor library makes no attempt to keep the data current. Keyset or dynamic cursors requery the database to fetch data from the database as you position over specific rows of the result set.

The DAO/Jet model supports a variety of cursors against remote data sources including:

  • Dynaset cursors are fully scrollable, updatable cursors with fixed membership and dynamic data values.

  • Snapshot cursors are fully scrolling, read-only cursors with fixed membership and static data values.

  • Forward-only cursors are nonscrolling, read-only cursors with fixed membership and static data values.

ODBCDirect adds Dynamic cursors to this list as implemented by RDO.

It always makes sense to create a cursor that has the least impact on your workstation and provides the best performance. The cursors created by DAO when using Jet can be expensive, in that the default dynaset-type Recordset object cursor is read/write, is fully scrolling, and supports updatable heterogeneous joins. By choosing the DAO Jet forward-only, read-only cursor, you can dramatically improve cursor performance. In contrast, the default ODBCDirect cursor is the most efficient, but offers fewer features because it defaults to a read-only forward-only recordset.