ADO - Resync method

In ADO (ActiveX Data Objects), the Resync method is used to refresh the data in the current record (or all records) of a Recordset from the underlying database — without re-executing the original query.

It’s like asking ADO to “re-check the database for updated values” for the same records you already have open.

Purpose

While Requery re-runs the entire query,
Resync only refreshes the existing records in the Recordset — it doesn’t fetch new or deleted rows.

It’s useful when:

You suspect some records in your Recordset may have been changed by another user.
You want to synchronize your Recordset’s data with the current database state.
You don’t want the overhead of completely requerying.

Syntax

recordset.Resync [AffectRecords], [ResyncValues]

Parameters:

Parameter	Description
`AffectRecords` (optional)	Specifies which records to refresh. Uses the AffectEnum constants.
`ResyncValues` (optional)	Specifies how to handle local changes vs. database changes. Uses ResyncEnum constants.

`AffectRecords` Constants:

Constant	Description
`adAffectCurrent`	Resync only the current record.
`adAffectGroup`	Resync all records that satisfy the current Filter.
`adAffectAll`	Resync all records in the Recordset.

`ResyncValues` Constants:

Constant	Description
`adResyncAllValues`	Refresh all fields from the database.
`adResyncUnderlyingValues`	Refresh only non-modified fields (preserve local changes).

Example 1: Resync the Current Record

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

rs.Open "SELECT * FROM Employees WHERE EmployeeID = 1", conn, adOpenKeyset, adLockOptimistic

' Suppose another user updates this record in the database

' Refresh only this record
rs.Resync adAffectCurrent, adResyncAllValues

Debug.Print "Updated salary: " & rs.Fields("Salary").Value

This updates only the current record’s values from the database.

Example 2: Resync All Records

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

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

' Refresh all records
rs.Resync adAffectAll, adResyncAllValues

This will synchronize all rows with the current database data,
but won’t include newly added or deleted rows — only updates to existing ones.

Example 3: Keep Local Changes

If you have made local edits (not yet updated to the database),
you can tell ADO to preserve those local values while refreshing others:

rs.Resync adAffectAll, adResyncUnderlyingValues

Local changes remain intact; only fields not modified locally get refreshed.

Key Difference: `Requery` vs. `Resync`

Feature	`Requery`	`Resync`
Action	Re-executes the entire query	Refreshes existing records only
New Records	Includes them	Excludes them
Deleted Records	Removes them	Keeps them (but might show errors if missing)
Local Edits	Discards all	Can preserve, depending on `ResyncValues`
Performance	Slower (full query rerun)	Faster (only data refresh)

Requirements and Notes

The Recordset must be updatable (adOpenKeyset or adOpenDynamic cursor type).
The Recordset must have unique identifiers (primary keys) to locate matching records.
If a record no longer exists in the database, it will raise a runtime error on resync.
Cannot be used with forward-only cursors.

Typical Use Case

In a multi-user environment:

If rs.EditMode = adEditNone Then
    rs.Resync adAffectCurrent
    MsgBox "Record updated with latest database values!"
End If

This ensures your local data reflects any changes other users made, without requerying the entire table.