By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 3,368 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

The Supports Method of an ADO Recordset Object

ADezii
Expert 5K+
P: 8,638
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.
Expand|Select|Wrap|Line Numbers
  1. Public Sub RecordsetSupport(rst As ADODB.Recordset)
  2. If rst.Supports(adAddNew) Then
  3.   Debug.Print "Supports AddNew"
  4. Else
  5.   Debug.Print "Doesn't support AddNew"
  6. End If
  7.  
  8. If rst.Supports(adApproxPosition) Then
  9.   Debug.Print "Supports AbsolutePosition"
  10. Else
  11.   Debug.Print "Doesn't support AbsolutePosition"
  12. End If
  13.  
  14. If rst.Supports(adBookmark) Then
  15.   Debug.Print "Supports Bookmarks"
  16. Else
  17.   Debug.Print "Doesn't support Bookmarks"
  18. End If
  19.  
  20. If rst.Supports(adDelete) Then
  21.   Debug.Print "Supports Delete"
  22. Else
  23.   Debug.Print "Doesn't support Delete"
  24. End If
  25.  
  26. If rst.Supports(adFind) Then
  27.   Debug.Print "Supports Find"
  28. Else
  29.   Debug.Print "Doesn't support Find"
  30. End If
  31.  
  32. If rst.Supports(adHoldRecords) Then
  33.   Debug.Print "Supports Move without Save"
  34. Else
  35.   Debug.Print "Doesn't support Move without Save"
  36. End If
  37.  
  38. If rst.Supports(adIndex) Then
  39.   Debug.Print "Supports Index"
  40. Else
  41.   Debug.Print "Doesn't support Index"
  42. End If
  43.  
  44. If rst.Supports(adMovePrevious) Then
  45.   Debug.Print "Supports MovePrevious"
  46. Else
  47.   Debug.Print "Doesn't support MovePrevious"
  48. End If
  49.  
  50. If rst.Supports(adResync) Then
  51.   Debug.Print "Supports Resync"
  52. Else
  53.   Debug.Print "Doesn't support Resync"
  54. End If
  55.  
  56. If rst.Supports(adSeek) Then
  57.   Debug.Print "Supports Seek"
  58. Else
  59.   Debug.Print "Doesn't support Seek"
  60. End If
  61.  
  62. If rst.Supports(adUpdate) Then
  63.   Debug.Print "Supports Update"
  64. Else
  65.   Debug.Print "Doesn't support Update"
  66. End If
  67.  
  68. If rst.Supports(adUpdateBatch) Then
  69.   Debug.Print "Supports UpdateBatch"
  70. Else
  71.   Debug.Print "Doesn't support UpdateBatch"
  72. End If
  73. End Sub
Expand|Select|Wrap|Line Numbers
  1. 'Recordset #1 (Restrictive)
  2. Dim rstSupports1 As ADODB.Recordset, strSQL As String
  3.  
  4. Set rstSupports1 = New ADODB.Recordset
  5.  
  6. strSQL = "Select * From Employees;"
  7.  
  8. With rstSupports1
  9.   .Source = strSQL
  10.   .ActiveConnection = CurrentProject.Connection
  11.   .CursorType = adOpenForwardOnly
  12.   .LockType = adLockReadOnly
  13. End With
  14.  
  15. rstSupports1.Open , , , , adCmdText
  16.  
  17. Debug.Print "-----------------------------------------------------------------------------------"
  18. Debug.Print "SQL Statement Source | Forward Only Cursor | Read Only Recordset | acCmdText Option"
  19. Debug.Print "-----------------------------------------------------------------------------------"
  20.  
  21. Call RecordsetSupport(rstSupports1)
  22.  
  23. rstSupports1.Close
  24. Set rstSupports1 = Nothing
Expand|Select|Wrap|Line Numbers
  1. 'Recordset #2 (Open)
  2. On Error GoTo Err_Command115_Click
  3.  
  4. Dim rstSupports2 As ADODB.Recordset
  5.  
  6. Set rstSupports2 = New ADODB.Recordset
  7.  
  8. With rstSupports2
  9.   .Source = "Employees"
  10.   .ActiveConnection = CurrentProject.Connection
  11.   .CursorType = adOpenKeyset
  12.   .LockType = adLockOptimistic
  13. End With
  14.  
  15. rstSupports2.Open , , , , adCmdTableDirect
  16.  
  17. Debug.Print "-----------------------------------------------------------------------------------"
  18. Debug.Print "Table Type Recordset | Keyset Cursor | Optimistic Locking | acCmdTableDirect Option"
  19. Debug.Print "-----------------------------------------------------------------------------------"
  20.  
  21. Call RecordsetSupport(rstSupports2)
  22.  
  23. rstSupports2.Close
  24. Set rstSupports2 = Nothing
OUTPUT from Recordset #1
Expand|Select|Wrap|Line Numbers
  1. -----------------------------------------------------------------------------------
  2. SQL Statement Source | Forward Only Cursor | Read Only Recordset | acCmdText Option
  3. -----------------------------------------------------------------------------------
  4. Doesn't support AddNew
  5. Doesn't support AbsolutePosition
  6. Doesn't support Bookmarks
  7. Doesn't support Delete
  8. Supports Find
  9. Doesn't support Move without Save
  10. Doesn't support Index
  11. Doesn't support MovePrevious
  12. Supports Resync
  13. Doesn't support Seek
  14. Doesn't support Update
  15. Doesn't support UpdateBatch
OUTPUT from Recordset #2
Expand|Select|Wrap|Line Numbers
  1. -----------------------------------------------------------------------------------
  2. Table Type Recordset | Keyset Cursor | Optimistic Locking | acCmdTableDirect Option
  3. -----------------------------------------------------------------------------------
  4. Supports AddNew
  5. Doesn't support AbsolutePosition
  6. Supports Bookmarks
  7. Supports Delete
  8. Supports Find
  9. Supports Move without Save
  10. Supports Index
  11. Supports MovePrevious
  12. Doesn't support Resync
  13. Supports Seek
  14. Supports Update
  15. Supports UpdateBatch
Oct 15 '07 #1
Share this Article
Share on Google+