ADO - ADO Recordset Supports Method and Provider Capability Detection

ADO Recordset

The Supports method in ActiveX Data Objects (ADO) is used to determine whether a particular Recordset object supports a specific feature or operation. Different ADO providers may offer different capabilities. Therefore, an application should not always assume that every Recordset supports operations such as bookmarks, updating, deleting, or scrolling in a particular direction. The Supports method allows the application to check these capabilities before attempting to use them.

1. What Is Provider Capability Detection?

When an ADO application connects to a database, ADO communicates with the database through an underlying data provider. The provider determines which operations and features are available for a particular connection or Recordset.

For example, one provider may support:

  • Updating records

  • Deleting records

  • Adding new records

  • Bookmarking records

  • Moving backward through records

  • Finding records

  • Resynchronizing records

  • Batch updates

Another provider may support only some of these features.

Instead of assuming that an operation is available, an application can use the Supports method to check the capability first.

The basic syntax is:

recordset.Supports(CursorOption)

The method returns a Boolean value:

True

if the requested capability is supported, and:

False

if it is not supported.

2. Why Is the Supports Method Important?

ADO works with different database systems and providers. Their behavior and capabilities can vary depending on the provider, cursor type, lock type, and Recordset configuration.

Suppose an application tries to use bookmarks without checking whether the Recordset supports them. If the provider does not support bookmarks, the application may produce an error or behave differently than expected.

Using Supports provides a safer approach:

If rs.Supports(adBookmark) Then
    ' Bookmark functionality can be used
Else
    ' Bookmark functionality is unavailable
End If

This makes an application more adaptable to different database environments.

3. CursorOption Argument

The Supports method accepts a CursorOptionEnum value that identifies the capability being tested.

For example:

rs.Supports(adBookmark)

checks whether the Recordset supports bookmarks.

Similarly:

rs.Supports(adAddNew)

checks whether new records can be added.

The important point is that the argument does not represent a database command. Instead, it represents a capability that the Recordset may or may not provide.

4. Common Capabilities Checked with Supports

Several ADO cursor options can be used with Supports.

adAddNew

Checks whether the Recordset supports adding new records.

If rs.Supports(adAddNew) Then
    rs.AddNew
End If

If the result is True, the application can use AddNew to create a new record.

adUpdate

Checks whether existing records can be updated.

If rs.Supports(adUpdate) Then
    rs!Name = "John"
    rs.Update
End If

This is particularly useful when the Recordset might be opened with different cursor or lock configurations.

adDelete

Checks whether records can be deleted.

If rs.Supports(adDelete) Then
    rs.Delete
End If

If the provider or Recordset configuration does not permit deletion, the application can avoid attempting the operation.

adBookmark

Checks whether the Recordset supports bookmarks.

If rs.Supports(adBookmark) Then
    currentBookmark = rs.Bookmark
End If

Bookmarks allow an application to identify a particular record and return to it later.

adMovePrevious

Checks whether the application can move backward through the Recordset.

If rs.Supports(adMovePrevious) Then
    rs.MovePrevious
End If

This can be useful when working with different cursor types because not every cursor provides the same navigation capabilities.

adFind

Checks whether the Recordset supports the Find operation.

If rs.Supports(adFind) Then
    rs.Find "Department = 'Sales'"
End If

This allows the application to determine whether provider-supported searching is available before calling Find.

5. Example of Capability Detection

Consider a program that retrieves employee information:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.Open "SELECT EmployeeID, Name, Department FROM Employees", _
        connectionObject, adOpenStatic, adLockOptimistic

If rs.Supports(adBookmark) Then
    Debug.Print "Bookmarks are supported."
Else
    Debug.Print "Bookmarks are not supported."
End If

If rs.Supports(adUpdate) Then
    Debug.Print "Records can be updated."
Else
    Debug.Print "Records cannot be updated."
End If

If rs.Supports(adDelete) Then
    Debug.Print "Records can be deleted."
Else
    Debug.Print "Records cannot be deleted."
End If

Here, the application does not blindly assume that all Recordset operations are available. It first asks the Recordset what capabilities it supports.

6. Supports Depends on the Recordset Configuration

An important concept is that capability detection is not simply a property of the database.

The result can depend on factors such as:

  • The database provider

  • The cursor type

  • The lock type

  • How the Recordset was opened

  • The SQL statement

  • Whether the Recordset is read-only

  • Provider-specific restrictions

For example, a Recordset opened with a configuration suitable for read-only browsing may not support updating, while another Recordset using an appropriate cursor and lock configuration may support it.

Therefore, the same database can produce different Supports results for differently configured Recordsets.

7. Supports Is Different from Checking Object State

The State property tells you whether an ADO object is currently open or closed.

For example:

If rs.State = adStateOpen Then
    ' Recordset is open
End If

The Supports method answers a different question:

If rs.Supports(adUpdate) Then
    ' Recordset supports updating
End If

The distinction is important.

State answers:

Is the Recordset currently open?

Supports answers:

Does this Recordset support this particular capability?

These two concepts should not be confused.

8. Supports Does Not Perform the Operation

The Supports method only checks capability. It does not actually execute the operation.

For example:

If rs.Supports(adDelete) Then
    rs.Delete
End If

The first statement checks whether deletion is supported.

The second statement actually deletes the record.

This makes Supports useful as a defensive programming mechanism.

9. Practical Application

Imagine an application that displays database records and provides Edit and Delete buttons.

When the Recordset is created, the application can check its capabilities:

CanEdit = rs.Supports(adUpdate)
CanDelete = rs.Supports(adDelete)
CanAdd = rs.Supports(adAddNew)

The user interface can then enable or disable corresponding operations.

For example:

Recordset capabilities:

Add New: Supported
Update: Supported
Delete: Not Supported
Bookmark: Supported

The application could allow the user to add and edit records while disabling the Delete operation.

This is more reliable than assuming that every database connection supports every operation.

10. Advantages of the Supports Method

The Supports method provides several benefits.

Better portability

An application may work with different ADO providers without assuming that all providers behave identically.

Fewer runtime errors

Checking a capability before using it can prevent unsupported operations from being attempted.

Flexible applications

Applications can adapt their behavior according to the capabilities of the current Recordset.

Easier provider compatibility

When changing database providers, capability checks can help identify differences in supported operations.

Improved user interfaces

Applications can enable or disable features such as Add, Edit, Delete, and navigation depending on what the Recordset supports.

11. Limitations

The Supports method should not be treated as an absolute guarantee that an operation will always succeed.

A True result indicates that the requested capability is supported by the Recordset, but the actual operation can still fail for other reasons.

For example, an update might fail because:

  • A database constraint is violated.

  • The connection has been lost.

  • Another transaction has changed the data.

  • The provider encounters an error.

  • The database rejects the submitted value.

Therefore, Supports should normally be combined with proper error handling.

For example:

If rs.Supports(adUpdate) Then
    On Error GoTo UpdateError

    rs!Department = "Sales"
    rs.Update
End If

Exit Sub

UpdateError:
    Debug.Print Err.Description

The Supports method checks whether updating is supported, while error handling deals with problems that occur during the actual update.

12. Supports Method vs. Provider Capability Detection

Provider capability detection is the broader concept, while the Supports method is one of the mechanisms ADO provides for performing that detection.

The relationship can be understood as:

Database
   |
   v
ADO Provider
   |
   v
ADO Recordset
   |
   v
Supports Method
   |
   +---- Add New?
   +---- Update?
   +---- Delete?
   +---- Bookmark?
   +---- Find?
   +---- Move Previous?

The application asks the Recordset whether a particular feature is available rather than assuming that the provider supports it.

13. Best Practices

When using the Supports method, follow these practices:

  1. Check capabilities before using optional Recordset features.

  2. Do not assume that every provider supports the same cursor functionality.

  3. Consider cursor and lock settings when interpreting capabilities.

  4. Use error handling even after Supports returns True.

  5. Use capability checks when building applications intended to work with multiple providers.

  6. Do not confuse Supports with the State property.

  7. Use the result to adapt application behavior rather than simply displaying it.

Conclusion

The ADO Recordset Supports method provides a way to determine whether a Recordset supports a particular operation or capability. This is especially important because ADO applications can work with different providers, cursor types, and locking configurations, each of which can affect available functionality.

By using statements such as rs.Supports(adUpdate), rs.Supports(adDelete), or rs.Supports(adBookmark), developers can determine what their current Recordset can do before attempting an operation. This makes ADO applications more portable, flexible, reliable, and resistant to provider-specific differences.