The Supports Method of an ADO Recordset Object

[ADezii]

When you create an ADO Recordset, you should have some idea as to what functionality the Recordset does/does not provide. Some critical questions may, and should, be:

1. Can I add New Records to the Recordset?
2. Does the Recordset support Bookmarks?
3. Can we use the Find and/or Seek Methods with this Recordset?
4. Does the Recordset support the use of Indexes?
5. Will the Absoluteposition property be able to be used on this Recordset?
6. etc....

Fortunately, all these questions and more can be easily answered by using the Supports Method of an ADO Recordset Object. This Method determines what kind of functionality an ADO Recordset Object supports. It returns a Boolean (True/False) value that indicates whether all the features identified by its single Argument are supported by the Provider. If a specific functionality is supported, it returns True, otherwise False.

To demonstrate the use of this Method, I will:

1. Display a 'Wrapper' Function Sub-Routine called RecordsetSupport() that accepts a single Argument (an ADO Recordset Object).
2. Create 2 very different ADO Recordsets.
3. Pass the Recordset Objects to the RecordsetSupport() Sub-Routine.
4. Display the Output for each Recordset indicating what functionality each supports.
5. The preceding steps will be performed for each Recordset in turn.

Public Sub RecordsetSupport(rst As ADODB.Recordset)
If rst.Supports(adAddNew) Then
  Debug.Print "Supports AddNew"
Else
  Debug.Print "Doesn't support AddNew"
End If
 
If rst.Supports(adApproxPosition) Then
  Debug.Print "Supports AbsolutePosition"
Else
  Debug.Print "Doesn't support AbsolutePosition"
End If
 
If rst.Supports(adBookmark) Then
  Debug.Print "Supports Bookmarks"
Else
  Debug.Print "Doesn't support Bookmarks"
End If
 
If rst.Supports(adDelete) Then
  Debug.Print "Supports Delete"
Else
  Debug.Print "Doesn't support Delete"
End If
 
If rst.Supports(adFind) Then
  Debug.Print "Supports Find"
Else
  Debug.Print "Doesn't support Find"
End If
 
If rst.Supports(adHoldRecords) Then
  Debug.Print "Supports Move without Save"
Else
  Debug.Print "Doesn't support Move without Save"
End If
 
If rst.Supports(adIndex) Then
  Debug.Print "Supports Index"
Else
  Debug.Print "Doesn't support Index"
End If
 
If rst.Supports(adMovePrevious) Then
  Debug.Print "Supports MovePrevious"
Else
  Debug.Print "Doesn't support MovePrevious"
End If
 
If rst.Supports(adResync) Then
  Debug.Print "Supports Resync"
Else
  Debug.Print "Doesn't support Resync"
End If
 
If rst.Supports(adSeek) Then
  Debug.Print "Supports Seek"
Else
  Debug.Print "Doesn't support Seek"
End If
 
If rst.Supports(adUpdate) Then
  Debug.Print "Supports Update"
Else
  Debug.Print "Doesn't support Update"
End If
 
If rst.Supports(adUpdateBatch) Then
  Debug.Print "Supports UpdateBatch"
Else
  Debug.Print "Doesn't support UpdateBatch"
End If
End Sub

'Recordset #1 (Restrictive)
Dim rstSupports1 As ADODB.Recordset, strSQL As String
 
Set rstSupports1 = New ADODB.Recordset
 
strSQL = "Select * From Employees;"
 
With rstSupports1
  .Source = strSQL
  .ActiveConnection = CurrentProject.Connection
  .CursorType = adOpenForwardOnly
  .LockType = adLockReadOnly
End With
 
rstSupports1.Open , , , , adCmdText
 
Debug.Print "-----------------------------------------------------------------------------------"
Debug.Print "SQL Statement Source | Forward Only Cursor | Read Only Recordset | acCmdText Option"
Debug.Print "-----------------------------------------------------------------------------------"
 
Call RecordsetSupport(rstSupports1)
 
rstSupports1.Close
Set rstSupports1 = Nothing

'Recordset #2 (Open)
On Error GoTo Err_Command115_Click
 
Dim rstSupports2 As ADODB.Recordset
 
Set rstSupports2 = New ADODB.Recordset
 
With rstSupports2
  .Source = "Employees"
  .ActiveConnection = CurrentProject.Connection
  .CursorType = adOpenKeyset
  .LockType = adLockOptimistic
End With
 
rstSupports2.Open , , , , adCmdTableDirect
 
Debug.Print "-----------------------------------------------------------------------------------"
Debug.Print "Table Type Recordset | Keyset Cursor | Optimistic Locking | acCmdTableDirect Option"
Debug.Print "-----------------------------------------------------------------------------------"
 
Call RecordsetSupport(rstSupports2)
 
rstSupports2.Close
Set rstSupports2 = Nothing

OUTPUT from Recordset #1

-----------------------------------------------------------------------------------
SQL Statement Source | Forward Only Cursor | Read Only Recordset | acCmdText Option
-----------------------------------------------------------------------------------
Doesn't support AddNew
Doesn't support AbsolutePosition
Doesn't support Bookmarks
Doesn't support Delete
Supports Find
Doesn't support Move without Save
Doesn't support Index
Doesn't support MovePrevious
Supports Resync
Doesn't support Seek
Doesn't support Update
Doesn't support UpdateBatch

OUTPUT from Recordset #2

-----------------------------------------------------------------------------------
Table Type Recordset | Keyset Cursor | Optimistic Locking | acCmdTableDirect Option
-----------------------------------------------------------------------------------
Supports AddNew
Doesn't support AbsolutePosition
Supports Bookmarks
Supports Delete
Supports Find
Supports Move without Save
Supports Index
Supports MovePrevious
Doesn't support Resync
Supports Seek
Supports Update
Supports UpdateBatch