[Excel VBA] How to Distinguish Between OpenRecordset Cursor Types

Introduction

When manipulating Access tables from Excel VBA, specifying the Cursor Type (RecordsetTypeEnum) in the second argument of OpenRecordset allows you to select behaviors best suited for your specific task, such as read-only access or forward-only scrolling.

In this article, I will introduce the five most frequently used constants and provide sample code using dbOpenSnapshot and dbOpenForwardOnly.

Main Cursor Types and Values

Tip:

You can specify the cursor type using either the constant name or its numeric value. When using Late Binding (calling DAO dynamically without setting a Reference), the numeric value is necessary because the constants are not defined in Excel VBA.

Sample Code: Comparing Snapshot and ForwardOnly

Sub OpenRecordsetWithCursorTypes()
    
    Dim daoEngine  As Object          ' DAO Engine
    Dim connDB     As Object          ' Access Database Connection
    Dim rsSnap     As Object          ' Recordset for Snapshot
    Dim rsFwd      As Object          ' Recordset for ForwardOnly
    Dim dbFile     As String          ' Path to Access File
    
    ' Full path to the Access file
    dbFile = ThisWorkbook.Path & "\inventory_data.accdb"
    
    ' Create DAO Engine and connect to Database
    Set daoEngine = CreateObject("DAO.DBEngine.120")
    Set connDB    = daoEngine.OpenDatabase(dbFile)
    
    ' --- Read-Only (Snapshot: Value 4) ---
    Set rsSnap = connDB.OpenRecordset("tbl_items", 4)
    
    ' Output headers to sheet range I1:K1
    Range("I1:K1").Value = Array("ItemID", "ItemName", "UnitPrice")
    
    ' Write the first record of the Snapshot to I2:K2
    If Not rsSnap.EOF Then
        Range("I2:K2").Value = Array(rsSnap!ItemID, rsSnap!ItemName, rsSnap!UnitPrice)
    End If
    rsSnap.Close
    
    ' --- High-Speed Reading (ForwardOnly: Value 8) ---
    Set rsFwd = connDB.OpenRecordset("tbl_items", 8)
    
    ' Get the 3rd record using ForwardOnly (Move 2 from 0-based index)
    rsFwd.Move 2
    If Not rsFwd.EOF Then
        Range("I3:K3").Value = Array(rsFwd!ItemID, rsFwd!ItemName, rsFwd!UnitPrice)
    End If
    rsFwd.Close
    
    ' Cleanup
    connDB.Close
    
End Sub

Explanation of the Code

• dbOpenSnapshot (4): Retrieves data statically as read-only. Ideally suited for list displays or summary reports where data does not need to be modified.
• dbOpenForwardOnly (8): Allows only one-way scrolling but is faster. Suitable for batch processing where data is read sequentially from the beginning.
• Move 2: Moves the cursor forward by 2 rows. Since the index is 0-based, this points to the 3rd record.

Guidelines for Differentiation

Summary

Appropriately selecting the OpenRecordset cursor type allows you to optimize reading speed and control over concurrent updates. In particular, Snapshot and ForwardOnly are useful for reducing processing time when handling large datasets. Use them according to your specific needs to enhance the efficiency of your Excel and Access integration.