ADO - Requery method

In ADO (ActiveX Data Objects), the Requery method is used to refresh or re-execute a Recordset’s query against the data source.

It’s a quick way to update the Recordset with the latest data from the database without having to close and reopen it.

Purpose

The Requery method re-runs the original SQL command or query that was used to open the Recordset, so that any changes in the underlying data (insertions, deletions, or updates) are reflected in the Recordset.

Syntax

recordset.Requery [Options]

recordset → The Recordset object you want to refresh.
Options (optional) → A constant that specifies how the requery should be executed.

Example 1: Simple Requery

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

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

' Display initial record count
Debug.Print "Before Requery: " & rs.RecordCount

' Suppose new records are added in the database here...

' Refresh the recordset
rs.Requery

' Display updated record count
Debug.Print "After Requery: " & rs.RecordCount

This re-runs the same SQL (SELECT * FROM Employees) and updates the Recordset with new data.

Example 2: With a Command Object

If the Recordset was opened using a Command object, Requery re-executes the command.

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

Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = "SELECT * FROM Products WHERE CategoryID = 1"

Set rs = cmd.Execute

' Refresh the data
rs.Requery

Example 3: Using the Options Parameter

The Options parameter controls how ADO re-executes the query. It accepts constants from the CommandTypeEnum, such as:

Constant	Description
`adCmdText`	SQL text command (default)
`adCmdTable`	Table name
`adCmdStoredProc`	Stored procedure name
`adCmdUnknown`	Type is unknown

Example:

rs.Requery adCmdText

Behavior Notes

Aspect	Description
Position	After requerying, the cursor moves to the first record.
Active Recordset	The Recordset must be open when calling `Requery`.
Updates	Requery discards all uncommitted edits.
Performance	Requery is faster than closing and reopening the Recordset, but still requires a round trip to the database.
Command association	The Recordset must have been created from a valid SQL query or Command.

Difference Between `Requery`, `Refresh`, and `Resync`

Method	Object	Purpose
`Requery`	`Recordset`	Re-executes the original query (reloads all data).
`Refresh`	`Connection` / `Recordset.Fields` / `Properties`	Refreshes the collection of items (not data).
`Resync`	`Recordset`	Updates current record(s) with latest values from database (without re-running the whole query).

Practical Use Case

For example, in a form that displays employees from a table:

Private Sub cmdRefresh_Click()
    If Not rs Is Nothing Then
        rs.Requery
        MsgBox "Data refreshed successfully!"
    End If
End Sub

This ensures that if another user adds or modifies employee records, your form will show the latest information without reopening the connection.

Key Points Recap:

Requery re-executes the original query for a Recordset.
It discards current edits and moves the cursor to the first record.
Use when you want to refresh data to reflect database changes.
Similar to pressing “Refresh” in a data grid or form.