ADO - Cursor Type

In ADO (ActiveX Data Objects), the CursorType property of a Recordset determines how you can move through the records, whether you can update them, and whether you can see changes made by other users. Essentially, it controls the behavior and flexibility of the Recordset.

1. Purpose

CursorType controls:

  • Navigation: Forward-only, backward, random access

  • Visibility of changes: Updates made by others

  • Updatability: Whether you can modify records

2. Syntax

rs.CursorType = adOpenForwardOnly   ' Set before opening the Recordset

or when opening a Recordset:

rs.Open "SELECT * FROM Employees", conn, adOpenStatic, adLockOptimistic

3. Cursor Types in ADO

Cursor Type Description Supports MovePrevious/MoveLast? Detects Changes by Others? Typical Use
adOpenForwardOnly (default) Only moves forward through records ❌ No ❌ No Fastest; simple read-only loops
adOpenKeyset Can move forward/back; sees changes made by others but not new records ✅ Yes ✅ Yes Moderate flexibility; allows updates
adOpenStatic Snapshot of data; can move in any direction; does not see changes made by others ✅ Yes ❌ No Useful for sorting, filtering, paging
adOpenDynamic Fully dynamic; sees all changes by others ✅ Yes ✅ Yes When you need real-time updates in multi-user apps

4. Example

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

' Using a scrollable static cursor
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic

rs.Open "SELECT * FROM Employees", conn

rs.MoveLast   ' Works because cursor is scrollable
MsgBox "Last employee: " & rs("EmployeeName")

5. Key Points

  • Forward-only is fastest and uses the least memory.

  • Static and keyset cursors allow backward navigation (MovePrevious, MoveLast).

  • Dynamic cursors reflect real-time changes in the database.

  • The choice of CursorType affects performance, updatability, and navigation.