Visual Basic Concepts

Building DAO Cursors with Jet

See Also

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:

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

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.