Microsoft Access 2010 Bible

Chapter 13: Accessing Data with VBA Code

497

Table 13.2 shows the permissible values for CursorType.

TABLE 13.2

CursorType Values

Value Effect of CursorType

adOpenDynamic A dynamic cursor supports all navigation methods, and the recordset is com-

pletely editable. New records can be added and existing records can be edited.

Changes made by other users are reflected in the recordset currently in memory.

adOpenForwardOnly The recordset is opened as a static copy of the underlying data, and new records

can’t be added. The recordset will also not reflect changes made to the underly-

ing tables by other users. Most important, only the MoveNext and MoveLast

methods are valid against a forward-only recordset.

adOpenKeyset Supports full navigation and records are editable. However, records added or

deleted by other users are not seen.

adOpenStatic Opens a static recordset that does not show changes made to the underlying

tables by other users. Similar to a forward-only cursor, except that all navigation

methods are valid.

Each type of cursor has a specific effect on the data contained in a recordset. For example, you

wouldn’t want to use a forward-only cursor on data where the user expects to be able to move for-

ward and backward through the data. A forward-only recordset is most often used for updating

records as a bulk operation, such as updating area codes or tax rates in a number of records.

On the other hand, it doesn’t make sense to use a dynamic cursor (adOpenDynamic) for simple

tasks such as scanning a recordset for updates. A dynamic cursor keeps track of changes by the

current user and changes in the underlying tables. A dynamic cursor is, therefore, slower and

requires memory and CPU cycles than a simpler forward-only cursor.

Detecting the recordset end or beginning

The MovePrevious and MoveNext methods move the current record pointer one row through

the recordset. If the pointer is at the very first or very last record, these methods move the pointer

off the beginning or end of the recordset without raising an error. When you’re navigating a

recordset, you need to be sure the current record pointer is resting on a valid record before refer-

encing data or executing an action on the record.

The ADO Recordset object support two Boolean properties, EOF and BOF, that indicate when

the current record pointer is at the end or beginning (respectively) of the recordset. (EOF and BOF

are acronyms for end of file and beginning of file.) EOF and BOF are both False when the record

pointer is on a valid record. EOF is True only when the record pointer is off the end of the record-

set, and BOF is True only when the pointer is off the beginning of the recordset. EOF and BOF are

both True only when the recordset contains no records at all.