ADO - ADO Recordset Status Property and Record Status Constants

The ADO Recordset Status property provides information about the current state of a record in an ADO Recordset. It is particularly useful when an application performs operations such as adding, modifying, or deleting records. Instead of simply knowing that an operation was attempted, the application can examine the status information to determine whether the operation succeeded, failed, or resulted in a particular condition. This becomes especially important when working with multiple records and batch updates.

1. What is the Status Property?

In ADO, the Status property is associated with a Recordset and can also provide status information for individual records through the Record object in newer ADO-related scenarios. For a Recordset, the Status property indicates the state of the current record or provides information about changes that have occurred.

A simplified example is:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM Employees", connectionObject

If rs.Status = adRecOK Then
    MsgBox "Record is in a normal state."
End If

The status value is represented using predefined ADO constants. These constants make it easier to understand what happened to a record without relying on numeric values directly.

2. Why Status Information is Important

Consider an application that allows a user to modify 100 customer records and then submits all those changes to a database. It is possible that some records are updated successfully while others encounter problems.

Without status information, the application may only know that the overall update operation encountered an error. With status information, the application can examine individual record states and determine which records:

• Were successfully updated

• Could not be updated

• Were affected by a conflict

• Were newly inserted

• Were modified

• Were deleted

• Need additional processing

This makes the Status property especially useful in applications that perform batch processing and batch updates.

3. Common ADO Record Status Constants

ADO provides several constants that describe record conditions. Some commonly encountered constants include:

Constant	Meaning
adRecOK	The record is in a normal state and no error has occurred.
adRecNew	The record is newly added.
adRecModified	The record has been modified.
adRecDeleted	The record has been deleted.
adRecUnmodified	The record has not been modified.
adRecInvalid	The record is invalid.
adRecMultipleChanges	Multiple changes have affected the record.
adRecPendingChanges	Changes to the record are waiting to be applied.
adRecCanceled	The operation affecting the record was canceled.
adRecConcurrencyViolation	A concurrency conflict occurred while processing the record.

The exact status can depend on the operation being performed and the provider being used.

4. Understanding adRecOK

adRecOK represents a normal record condition.

For example:

If rs.Status = adRecOK Then
    MsgBox "The record is valid."
End If

This can be useful after an operation when the application needs to determine whether the record remains in an acceptable state.

However, applications should not assume that every successful database operation can be represented by only one status value. ADO status values can represent different states, particularly when multiple changes or batch operations are involved.

5. Understanding adRecNew

The adRecNew constant indicates that a record is newly added.

For example, an application might create a new employee record:

rs.AddNew
rs("Name") = "John"
rs("Department") = "Sales"

Before the changes are permanently applied, the record may have a status indicating that it is new.

This allows an application to distinguish newly created records from existing records that have simply been modified.

6. Understanding adRecModified

When an existing record is changed, ADO can indicate that the record has been modified.

For example:

rs("Salary") = 50000

The record was previously stored in the database, but its value has now been changed in the Recordset.

A status such as adRecModified can therefore help an application identify records that contain pending modifications.

This is particularly useful when an application needs to process only records that have actually changed.

7. Understanding adRecDeleted

When a record is marked for deletion, ADO can identify it using a deleted-record status.

For example:

rs.Delete

The application can use status information to distinguish deleted records from records that remain active.

This is useful in applications where changes are collected first and submitted to the database later.

8. Understanding adRecUnmodified

adRecUnmodified indicates that a record has not been changed.

Suppose a Recordset contains 50 records and a user modifies only five of them. Status information can help distinguish the five modified records from the remaining unmodified records.

This can reduce unnecessary processing because the application does not need to treat every record as changed.

9. Understanding Pending Changes

One important use of status information is identifying changes that have not yet been successfully applied to the underlying data source.

For example:

rs.UpdateBatch

When batch updating is used, changes can be accumulated in the Recordset and submitted together.

If some changes cannot be applied, the application can examine the status information to determine which records still have pending changes.

A simplified approach is:

rs.UpdateBatch

If rs.Status = adRecPendingChanges Then
    MsgBox "Some changes are still pending."
End If

The actual handling of status values should take into account the specific provider and update mode being used.

10. Concurrency Violations

A particularly important status is adRecConcurrencyViolation.

A concurrency violation can occur when two users or processes attempt to modify the same database record.

For example, imagine that User A retrieves an employee record showing a salary of 40,000. User B retrieves the same record and changes the salary to 45,000. User A then attempts to update the record using the older information.

Depending on the provider and concurrency configuration, ADO may detect that the underlying record has changed since User A retrieved it.

The application can then identify the record as having a concurrency-related problem and ask the user to review the latest information.

11. Status Values and Bitwise Combinations

An important point about ADO status values is that status information can sometimes contain multiple flags at the same time.

Therefore, checking a status value using only a simple equality comparison may not always be sufficient.

For example, instead of assuming:

If rs.Status = adRecModified Then

an application may need to test whether a particular flag is present.

Conceptually:

If (rs.Status And adRecModified) <> 0 Then
    MsgBox "The record has been modified."
End If

This approach is useful when several status conditions can coexist.

12. Status and Batch Updates

The Status property becomes particularly valuable when using batch updating.

A typical sequence can be:

Open Recordset
       |
Modify multiple records
       |
Check record states
       |
Submit changes
       |
UpdateBatch
       |
Examine status information
       |
Handle failed or conflicting records

Suppose an application modifies 20 records. Nineteen updates may succeed while one record encounters a conflict.

Instead of treating the entire operation as a simple success or failure, the application can inspect the relevant status information and identify the problematic record.

This provides much better control over error handling.

13. Example of Practical Status Handling

Consider an employee-management application:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.Open "SELECT EmployeeID, Name, Salary FROM Employees", _
        connectionObject, adOpenStatic, adLockBatchOptimistic

rs.MoveFirst

Do Until rs.EOF

    If (rs.Status And adRecModified) <> 0 Then
        Debug.Print "Modified employee: " & rs("EmployeeID")
    End If

    rs.MoveNext
Loop

The application can use the status information to identify records that have been modified before submitting the changes.

After modifications have been submitted, the application can perform additional status checking to identify records requiring further attention.

14. Difference Between Status and Errors

The Status property and ADO error handling are related, but they serve different purposes.

The Status property describes the state of a record or operation.

The Errors collection provides more detailed information about errors returned by the provider.

For example, a record might have a concurrency-related status. The Errors collection may additionally contain information explaining why the provider rejected the update.

Therefore, robust applications often use both mechanisms:

Status
  |
  +-- What happened to the record?

Errors Collection
  |
  +-- Why did the operation fail?

This combination provides a more complete error-handling strategy.

15. Advantages of Using Status Information

The ADO Status property provides several benefits:

1. It helps identify modified records.

2. It helps distinguish new and deleted records.

3. It supports more effective batch-update processing.

4. It helps detect concurrency conflicts.

5. It allows applications to handle individual record states.

6. It reduces unnecessary database processing.

7. It improves error-handling logic.

8. It makes complex data-update operations easier to monitor.

16. Limitations and Considerations

ADO status behavior can vary depending on the OLE DB provider, cursor type, locking mode, and type of operation being performed.

Therefore, developers should not assume that every provider supports every status condition identically.

Applications should also avoid depending exclusively on numeric status values. Using named ADO constants makes the code much easier to understand and maintain.

For production applications, status checking should generally be combined with proper error handling and validation of the underlying database operation.

Conclusion

The ADO Recordset Status property and record status constants provide a mechanism for understanding what is happening to records during data manipulation. Instead of treating a Recordset simply as a collection of rows, developers can use status information to determine whether records are new, modified, deleted, unchanged, pending, canceled, or affected by concurrency problems.

This becomes especially valuable when applications perform batch updates, multi-user data modifications, and detailed record-level error handling. By combining status information with ADO's Errors collection, developers can build database applications that respond more intelligently to successful updates, failed operations, and conflicting changes.