ADO - ADO Recordset Open Options and Open Parameters

The ADO Recordset Open method is used to create and open a Recordset so that an application can retrieve, navigate, and sometimes modify data obtained from a database. The Open method is important because it determines where the data comes from, which database connection is used, how the records can be navigated, and how the records are locked.

In classic ADO, the general syntax is:

Recordset.Open Source, ActiveConnection, CursorType, LockType, Options

Each parameter has a specific purpose:

Open(
    Source,
    ActiveConnection,
    CursorType,
    LockType,
    Options
)

Understanding these parameters is essential because changing the cursor or locking options can significantly affect the behavior and performance of a Recordset.

1. Source Parameter

The Source parameter specifies what data should be opened in the Recordset. It can generally be a SQL statement, table name, stored procedure, or a Command object.

For example:

rs.Open "SELECT * FROM Employees", conn

Here:

"SELECT * FROM Employees"

is the Source.

The database executes the SQL query and returns the resulting records to the Recordset.

A simple filtering query can also be used:

rs.Open "SELECT * FROM Employees WHERE Department = 'Sales'", conn

The Source does not necessarily have to be a SQL string. It can also be a Command object:

rs.Open cmd, conn

This approach is useful when a query requires parameters or when the application needs to separate command preparation from Recordset processing.

2. ActiveConnection Parameter

The ActiveConnection parameter specifies the database connection through which the Source is executed.

For example:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection

conn.Open "Provider=SQLOLEDB;Data Source=Server01;Initial Catalog=CompanyDB;Integrated Security=SSPI"

Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM Employees", conn

Here, conn is the ActiveConnection.

The connection must normally be established before opening the Recordset.

An alternative is to provide a connection string directly:

rs.Open "SELECT * FROM Employees", _
        "Provider=SQLOLEDB;Data Source=Server01;Initial Catalog=CompanyDB;Integrated Security=SSPI"

Using an existing Connection object is generally easier to manage when an application performs multiple database operations.

3. CursorType Parameter

The CursorType determines how the Recordset can navigate through records and what changes made by other users can be observed.

Common cursor types include:

adOpenForwardOnly

This cursor allows movement primarily from the beginning toward the end of the Recordset.

rs.Open "SELECT * FROM Employees", conn, adOpenForwardOnly

It is generally efficient when the application only needs to read records sequentially.

For example, displaying every employee once is a suitable use case.

Do Until rs.EOF
    Debug.Print rs("EmployeeName")
    rs.MoveNext
Loop

adOpenKeyset

A Keyset cursor allows movement in multiple directions and provides a view based on the set of records that existed when the cursor was created.

rs.Open "SELECT * FROM Employees", _
        conn, adOpenKeyset

It is useful when the application needs more navigation capabilities than a forward-only cursor.

adOpenDynamic

A Dynamic cursor provides a more dynamic view of the underlying data.

rs.Open "SELECT * FROM Employees", _
        conn, adOpenDynamic

Depending on the provider, changes made by other users may become visible while the Recordset remains open.

Provider support for cursor behavior can vary, so the requested cursor type is not always guaranteed to be fully supported.

adOpenStatic

A Static cursor provides a relatively fixed view of the data.

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

It is useful when the application needs to navigate backward and forward without requiring continuous visibility of changes made by other users.

4. LockType Parameter

The LockType specifies how records are locked when the application performs updates.

Common values include:

adLockReadOnly
adLockPessimistic
adLockOptimistic
adLockBatchOptimistic

adLockReadOnly

The Recordset is opened for reading only.

rs.Open "SELECT * FROM Employees", _
        conn, adOpenStatic, adLockReadOnly

The application cannot normally modify the records through this Recordset.

This is appropriate when the application only needs to display or analyze data.

adLockPessimistic

With pessimistic locking, a record can be locked while it is being edited.

rs.Open "SELECT * FROM Employees", _
        conn, adOpenKeyset, adLockPessimistic

The basic idea is:

User starts editing
        |
Record becomes locked
        |
User completes update
        |
Lock is released

This can reduce the possibility of two users simultaneously changing the same record, but it can also increase locking and reduce concurrency.

adLockOptimistic

With optimistic locking, the record is generally not locked for the entire editing period.

rs.Open "SELECT * FROM Employees", _
        conn, adOpenKeyset, adLockOptimistic

The application assumes that conflicts are relatively uncommon.

The basic process is:

User reads record
        |
User modifies record
        |
Application submits update
        |
Database checks and processes update

Optimistic locking is often preferable in applications where many users need to work with the same database simultaneously.

adLockBatchOptimistic

This mode allows changes to be accumulated and submitted as a batch.

rs.Open "SELECT * FROM Employees", _
        conn, adOpenStatic, adLockBatchOptimistic

The application can make several changes and later submit them together using an appropriate batch-update operation.

This can be useful when working with disconnected or batch-oriented data operations.

5. Options Parameter

The Options parameter provides additional information about how ADO should interpret the Source or execute the operation.

For example, when the Source is a SQL statement, the application can indicate that the Source should be interpreted as a command text.

rs.Open "SELECT * FROM Employees", _
        conn, adOpenStatic, adLockReadOnly, adCmdText

Here:

adCmdText

indicates that the Source is command text, such as SQL.

If the Source represents a table, another option can be used:

adCmdTable

For example:

rs.Open "Employees", _
        conn, adOpenStatic, adLockReadOnly, adCmdTable

The exact options available and their behavior depend partly on the ADO provider.

6. Combining the Parameters

The real power of the Open method comes from combining these parameters.

For example:

rs.Open "SELECT EmployeeID, EmployeeName FROM Employees", _
        conn, _
        adOpenStatic, _
        adLockReadOnly, _
        adCmdText

This means:

Source          = SQL SELECT statement
Connection      = conn
Cursor Type     = Static
Lock Type       = Read Only
Options         = Command Text

The resulting Recordset is intended for navigating through the returned data without modifying it.

7. Example for Updating Records

Suppose an application needs to retrieve employees and update their salaries.

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.Open "SELECT EmployeeID, EmployeeName, Salary FROM Employees", _
        conn, _
        adOpenKeyset, _
        adLockOptimistic, _
        adCmdText

If Not rs.EOF Then
    rs.MoveFirst

    rs("Salary") = rs("Salary") + 5000
    rs.Update
End If

The important part is:

adOpenKeyset
adLockOptimistic

The cursor allows more flexible navigation, while optimistic locking permits the application to make changes without maintaining a pessimistic lock throughout the editing period.

8. Using the Open Method With a Command Object

A Command object can be used as the Source.

For example:

Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set cmd = New ADODB.Command

Set cmd.ActiveConnection = conn

cmd.CommandText = _
    "SELECT * FROM Employees WHERE Department = ?"

cmd.CommandType = adCmdText

Set rs = New ADODB.Recordset

rs.Open cmd, conn, adOpenStatic, adLockReadOnly

Using a Command object becomes particularly useful when the database operation has parameters or requires more control over command execution.

9. Open Options and Performance

The choice of Open parameters can affect application performance.

For example, if an application only needs to read thousands of records sequentially, a forward-only, read-only Recordset may be more appropriate:

rs.Open "SELECT * FROM Employees", _
        conn, _
        adOpenForwardOnly, _
        adLockReadOnly, _
        adCmdText

This avoids requesting unnecessary update and navigation capabilities.

On the other hand, an application that needs to edit records may require something such as:

adOpenKeyset
adLockOptimistic

Therefore, the application should request only the functionality it actually needs.

10. Error Handling During Open

Opening a Recordset can fail for several reasons:

  • Invalid SQL syntax

  • Invalid connection

  • Database server unavailable

  • Missing table

  • Missing permissions

  • Unsupported cursor type

  • Unsupported locking mode

  • Provider-specific limitations

A basic error-handling example is:

On Error GoTo ErrorHandler

rs.Open "SELECT * FROM Employees", _
        conn, _
        adOpenStatic, _
        adLockReadOnly, _
        adCmdText

Exit Sub

ErrorHandler:
    MsgBox "Unable to open Recordset: " & Err.Description

In production applications, database errors should be handled carefully so that connection and Recordset objects are properly released.

11. Closing the Recordset

After completing the operation, the Recordset should be closed:

If Not rs Is Nothing Then
    If rs.State = adStateOpen Then
        rs.Close
    End If
End If

The Connection can then be closed when it is no longer needed:

If conn.State = adStateOpen Then
    conn.Close
End If

Properly closing database objects helps prevent unnecessary resource consumption.

12. Important Relationship Between CursorType and LockType

The CursorType and LockType parameters should not be considered independently.

For example:

adOpenForwardOnly

is primarily designed for sequential access, while:

adLockReadOnly

is designed for reading without modification.

A Recordset intended for editing might instead use:

adOpenKeyset
adLockOptimistic

Therefore, the appropriate combination depends on the application's requirements.

A useful way to remember their roles is:

Source           → What data should I retrieve?
ActiveConnection → Which database connection should I use?
CursorType       → How should I navigate through the records?
LockType         → How should record updates be handled?
Options          → How should ADO interpret the Source?

13. Complete Example

The following example demonstrates the Open method with all major parameters:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.Open _
    "Provider=SQLOLEDB;" & _
    "Data Source=Server01;" & _
    "Initial Catalog=CompanyDB;" & _
    "Integrated Security=SSPI"

rs.Open _
    "SELECT EmployeeID, EmployeeName, Department FROM Employees", _
    conn, _
    adOpenStatic, _
    adLockReadOnly, _
    adCmdText

Do Until rs.EOF

    Debug.Print rs("EmployeeID")
    Debug.Print rs("EmployeeName")
    Debug.Print rs("Department")

    rs.MoveNext

Loop

rs.Close
conn.Close

Set rs = Nothing
Set conn = Nothing

In this example, the application opens a SQL query using an existing Connection object. A Static cursor is used for navigation, the Recordset is read-only, and adCmdText tells ADO that the Source is command text.

14. Summary

The ADO Recordset Open method controls how a Recordset is created and accessed. Its five major parameters are Source, ActiveConnection, CursorType, LockType, and Options. The Source identifies the data operation, ActiveConnection identifies the database connection, CursorType controls record navigation, LockType controls update and locking behavior, and Options provides additional instructions about how ADO should interpret the Source.

Understanding these parameters is particularly important when developing applications that need efficient database access. Selecting a simple combination such as adOpenForwardOnly and adLockReadOnly can be appropriate for read-only sequential processing, while combinations such as adOpenKeyset and adLockOptimistic are more suitable when records need to be navigated and updated.