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

Retrieving Data From an ADO Recordset Using GetRows()

ADezii
Expert 5K+
P: 8,638
[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()
[CODE EXAMPLE]
Expand|Select|Wrap|Line Numbers
  1. Dim rstEmployees As ADODB.Recordset
  2. Dim varEmployees As Variant
  3. Dim intRowNum As Integer
  4. Dim intColNum As Integer
  5.  
  6. 'Make up of qryEmployees (5 Fields/9 Records) based on the
  7. 'sample Northwind.mdb Database
  8.   '[LastName] - Ascending
  9.   '[FirstName] - Ascending
  10.   '[Address]
  11.   '[City]
  12.   '[Region]
  13.  
  14. Set rstEmployees = New ADODB.Recordset
  15.  
  16. With rstEmployees
  17.   .Source = "qryEmployees"
  18.   .ActiveConnection = CurrentProject.Connection
  19.   .CursorType = adOpenKeyset
  20.   .LockType = adLockOptimistic
  21.     .Open
  22.       .MoveLast
  23.       .MoveFirst
  24. End With
  25.  
  26. 'Let's retrieve ALL Rows in the rstEmployees Recordset
  27. varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount)
  28.  
  29. 'Demonstration of the Fields Parameter of GetRows()
  30.  
  31. 'Let's retrieve only the LastName Field in the rstEmployees Recordset
  32. 'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, , "LastName")
  33.  
  34. 'Let's retrieve only the 3rd Field ([Address]) in the rstEmployees Recordset
  35. 'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, , 2)
  36.  
  37. 'Let's retrieve the [Address], [City], and [Region Fields by passing an Array
  38. 'of these Field Names as the Fields Parameter
  39. 'Dim avarFieldNames
  40. 'avarFieldNames = Array("Address", "City", "Region")
  41. 'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, , avarFieldNames)
  42.  
  43. 'Let's retrieve the [Address], [City], and [Region Fields by passing an Array
  44. 'of the Ordinal Positions of these Fields to the Fields Parameter
  45. 'Dim avarFieldNames(1 To 3) As Variant
  46. 'avarFieldNames(1) = 2
  47. 'avarFieldNames(2) = 3
  48. 'avarFieldNames(3) = 4
  49. 'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, , avarFieldNames)
  50. '*****************************************************************
  51. 'Demonstration of the Bookmark Parameter of GetRows()
  52.  
  53. 'Retrieve only those Records from thew Current Bookmark on
  54. 'Dim varBkMrk As Variant
  55. 'rstEmployees.Find "[LastName]='King'"
  56. 'varBkMrk = rstEmployees.Bookmark
  57.  
  58. 'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, varBkMrk)
  59.  
  60. '*****************************************************************
  61. 'Let's retrieve the first 6 Rows in the rstEmployees Recordset
  62. 'varEmployees = rstEmployees.GetRows(6)
  63.  
  64. 'If fewer than the desired number of Reows were returned
  65. If rstEmployees.RecordCount > UBound(varEmployees, 2) + 1 Then
  66.   MsgBox "Fewer Rows were returned than those requested"
  67. End If
  68.  
  69. '1st Row is the 0 Element of the Array, so we need the +1
  70. '2nd Subscript (2) identifies the Row Number
  71. Debug.Print "Number of Rows Retrieved: " & UBound(varEmployees, 2) + 1
  72.  
  73. Debug.Print
  74.  
  75. '1st Field is the 0 Element of the Array, so we need the +1
  76. '1st Subscript (1) identifies the Field
  77. Debug.Print "Number of Fields Retrieved: " & UBound(varEmployees, 1) + 1
  78.  
  79. Debug.Print
  80.  
  81. 'Let's retrieve the value of the 3rd Field ([Address]) in Row 5
  82. Debug.Print "Field 3 - Row 5: " & varEmployees(2, 4)
  83. 'Let's retrieve the value of the 1st Field ([LastName]) in Row 2
  84. Debug.Print "Field 1 - Row 2: " & varEmployees(0, 1)
  85.  
  86. Debug.Print
  87.  
  88. 'Debug.Print "******************************************"       'Column Format only
  89. Debug.Print "Last Name", "First Name", "Address", , "City", "Region"
  90. Debug.Print "---------------------------------------------------------------------------------------------"
  91. For intRowNum = 0 To UBound(varEmployees, 2)        'Loop thru each Row
  92.   For intColNum = 0 To UBound(varEmployees, 1)      'Loop thru each Column
  93.     'To Print Fields in Column Format with numbered Field and Row
  94.     'Debug.Print "Record#:" & intRowNum + 1 & "/Field#:" & intColNum + 1 & " ==> " & _
  95.                  'varEmployees(intColNum, intRowNum)
  96.     'To Print in Table Format, no numbered Fields or Rows
  97.     Debug.Print varEmployees(intColNum, intRowNum),
  98.   Next
  99.   Debug.Print vbCrLf
  100.   'Debug.Print "******************************************"     'Column Format only
  101. Next
  102.  
  103. rstEmployees.Close
  104. Set rstEmployees = Nothing
[NOTE]
In order to simplify matters for everyone, I've made the sample Database that I used to create both Tips #49 and #50, (DAO and ADO GetRows() Method), available as an Attachment for anyone who is interested to download.
Apr 17 '08 #1
Share this Article
Share on Google+
3 Comments


P: 18
@ADezii
i friend i am very interesting to yuor code...please send me a copy of your project. gss.italyATiol.it
Many tks.
Sal
Jan 31 '09 #2

ADezii
Expert 5K+
P: 8,638
@sal21
There is no Project to send. Everything you need codewise is in Post #9.
Feb 1 '09 #3

P: 18
@ADezii
please link of post#9 i dont see that(!?);-(
Feb 1 '09 #4