Retrieving Data From an ADO Recordset Using GetRows()

Originally Posted by ADezii

[OVERVIEW]
Last Tip, we demonstrated the technique for retrieving data from a DAO Recordset, and placing it into a 2-dimensional Array using the GetRows() Method. This week, we will cover the same exact Method (GetRows()), but only as it applies to an ADO Recordset. Although there are similarities in the 2 methodologies, the ADO Method offers 2 more Optional Arguments, is a little more complex, and of course, the syntax is different in creating the Recordset. The differences will be noted here along with some similarities, if you wish to see a General Overview of GetRows(), please reference the previous Tip (#49).

[SYNTAX OF ADO GETROWS() VERSION]

• array = Recordset.GetRows(Rows, Start, Fields)
  • Rows - (Optional), indicates the number of Records to retrieve, and defaults to all Records.
  • Start - (Optional), a String or Variant that evaluates to the Bookmark for the Record from which the GetRows() operation should begin.
  • Fields - (Optional), a Variant that represents a single Field Name, Ordinal Field position, Array of Field Names, or an Array of Ordinal Field positions. Only the data in these Fields are returned by GetRows()

1. Dim rstEmployees As ADODB.Recordset
2. Dim varEmployees As Variant
3. Dim intRowNum As Integer
4. Dim intColNum As Integer
5. 'Make up of qryEmployees (5 Fields/9 Records) based on the
6. 'sample Northwind.mdb Database
7. '[LastName] - Ascending
8. '[FirstName] - Ascending
9. '[Address]
10. '[City]
11. '[Region]
12. Set rstEmployees = New ADODB.Recordset
13. With rstEmployees
14. .Source = "qryEmployees"
15. .ActiveConnection = CurrentProject.Connection
16. .CursorType = adOpenKeyset
17. .LockType = adLockOptimistic
18. .Open
19. .MoveLast
20. .MoveFirst
21. End With
22. 'Let's retrieve ALL Rows in the rstEmployees Recordset
23. varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount)
24. 'Demonstration of the Fields Parameter of GetRows()
25. 'Let's retrieve only the LastName Field in the rstEmployees Recordset
26. 'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, , "LastName")
27. 'Let's retrieve only the 3rd Field ([Address]) in the rstEmployees Recordset
28. 'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, , 2)
29. 'Let's retrieve the [Address], [City], and [Region Fields by passing an Array
30. 'of these Field Names as the Fields Parameter
31. 'Dim avarFieldNames
32. 'avarFieldNames = Array("Address", "City", "Region")
33. 'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, , avarFieldNames)
34. 'Let's retrieve the [Address], [City], and [Region Fields by passing an Array
35. 'of the Ordinal Positions of these Fields to the Fields Parameter
36. 'Dim avarFieldNames(1 To 3) As Variant
37. 'avarFieldNames(1) = 2
38. 'avarFieldNames(2) = 3
39. 'avarFieldNames(3) = 4
40. 'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, , avarFieldNames)
41. '************************************************* ****************
42. 'Demonstration of the Bookmark Parameter of GetRows()
43. 'Retrieve only those Records from thew Current Bookmark on
44. 'Dim varBkMrk As Variant
45. 'rstEmployees.Find "[LastName]='King'"
46. 'varBkMrk = rstEmployees.Bookmark
47. 'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, varBkMrk)
48. '************************************************* ****************
49. 'Let's retrieve the first 6 Rows in the rstEmployees Recordset
50. 'varEmployees = rstEmployees.GetRows(6)
51. 'If fewer than the desired number of Reows were returned
52. If rstEmployees.RecordCount > UBound(varEmployees, 2) + 1 Then
53. MsgBox "Fewer Rows were returned than those requested"
54. End If
55. '1st Row is the 0 Element of the Array, so we need the +1
56. '2nd Subscript (2) identifies the Row Number
57. Debug.Print "Number of Rows Retrieved: " & UBound(varEmployees, 2) + 1
58. Debug.Print
59. '1st Field is the 0 Element of the Array, so we need the +1
60. '1st Subscript (1) identifies the Field
61. Debug.Print "Number of Fields Retrieved: " & UBound(varEmployees, 1) + 1
62. Debug.Print
63. 'Let's retrieve the value of the 3rd Field ([Address]) in Row 5
64. Debug.Print "Field 3 - Row 5: " & varEmployees(2, 4)
65. 'Let's retrieve the value of the 1st Field ([LastName]) in Row 2
66. Debug.Print "Field 1 - Row 2: " & varEmployees(0, 1)
67. Debug.Print
68. 'Debug.Print "******************************************" 'Column Format only
69. Debug.Print "Last Name", "First Name", "Address", , "City", "Region"
70. Debug.Print "---------------------------------------------------------------------------------------------"
71. For intRowNum = 0 To UBound(varEmployees, 2) 'Loop thru each Row
72. For intColNum = 0 To UBound(varEmployees, 1) 'Loop thru each Column
73. 'To Print Fields in Column Format with numbered Field and Row
74. 'Debug.Print "Record#:" & intRowNum + 1 & "/Field#:" & intColNum + 1 & " ==> " & _
75. 'varEmployees(intColNum, intRowNum)
76. 'To Print in Table Format, no numbered Fields or Rows
77. Debug.Print varEmployees(intColNum, intRowNum),
78. Next
79. Debug.Print vbCrLf
80. 'Debug.Print "******************************************" 'Column Format only
81. Next
82. rstEmployees.Close
83. Set rstEmployees = Nothing

Originally Posted by sal21

i friend i am very interesting to yuor code...please send me a copy of your project. gss.italyATiol.it
Many tks.
Sal

Originally Posted by ADezii

There is no Project to send. Everything you need codewise is in Post #9.