Retrieving Data From an ADO Recordset Using GetRows()

[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.GetRo ws(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]

Dim rstEmployees As ADODB.Recordset
Dim varEmployees As Variant
Dim intRowNum As Integer
Dim intColNum As Integer
 
'Make up of qryEmployees (5 Fields/9 Records) based on the
'sample Northwind.mdb Database
  '[LastName] - Ascending
  '[FirstName] - Ascending
  '[Address]
  '[City]
  '[Region]
 
Set rstEmployees = New ADODB.Recordset
 
With rstEmployees
  .Source = "qryEmployees"
  .ActiveConnection = CurrentProject.Connection
  .CursorType = adOpenKeyset
  .LockType = adLockOptimistic
    .Open
      .MoveLast
      .MoveFirst
End With
 
'Let's retrieve ALL Rows in the rstEmployees Recordset
varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount)
 
'Demonstration of the Fields Parameter of GetRows()
 
'Let's retrieve only the LastName Field in the rstEmployees Recordset
'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, , "LastName")
 
'Let's retrieve only the 3rd Field ([Address]) in the rstEmployees Recordset
'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, , 2)
 
'Let's retrieve the [Address], [City], and [Region Fields by passing an Array
'of these Field Names as the Fields Parameter
'Dim avarFieldNames
'avarFieldNames = Array("Address", "City", "Region")
'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, , avarFieldNames)
 
'Let's retrieve the [Address], [City], and [Region Fields by passing an Array
'of the Ordinal Positions of these Fields to the Fields Parameter
'Dim avarFieldNames(1 To 3) As Variant
'avarFieldNames(1) = 2
'avarFieldNames(2) = 3
'avarFieldNames(3) = 4
'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, , avarFieldNames)
'*****************************************************************
'Demonstration of the Bookmark Parameter of GetRows()
 
'Retrieve only those Records from thew Current Bookmark on
'Dim varBkMrk As Variant
'rstEmployees.Find "[LastName]='King'"
'varBkMrk = rstEmployees.Bookmark
 
'varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount, varBkMrk)
 
'*****************************************************************
'Let's retrieve the first 6 Rows in the rstEmployees Recordset
'varEmployees = rstEmployees.GetRows(6)
 
'If fewer than the desired number of Reows were returned
If rstEmployees.RecordCount > UBound(varEmployees, 2) + 1 Then
  MsgBox "Fewer Rows were returned than those requested"
End If
 
'1st Row is the 0 Element of the Array, so we need the +1
'2nd Subscript (2) identifies the Row Number
Debug.Print "Number of Rows Retrieved: " & UBound(varEmployees, 2) + 1
 
Debug.Print
 
'1st Field is the 0 Element of the Array, so we need the +1
'1st Subscript (1) identifies the Field
Debug.Print "Number of Fields Retrieved: " & UBound(varEmployees, 1) + 1
 
Debug.Print
 
'Let's retrieve the value of the 3rd Field ([Address]) in Row 5
Debug.Print "Field 3 - Row 5: " & varEmployees(2, 4)
'Let's retrieve the value of the 1st Field ([LastName]) in Row 2
Debug.Print "Field 1 - Row 2: " & varEmployees(0, 1)
 
Debug.Print
 
'Debug.Print "******************************************"       'Column Format only
Debug.Print "Last Name", "First Name", "Address", , "City", "Region"
Debug.Print "---------------------------------------------------------------------------------------------"
For intRowNum = 0 To UBound(varEmployees, 2)        'Loop thru each Row
  For intColNum = 0 To UBound(varEmployees, 1)      'Loop thru each Column
    'To Print Fields in Column Format with numbered Field and Row
    'Debug.Print "Record#:" & intRowNum + 1 & "/Field#:" & intColNum + 1 & " ==> " & _
                 'varEmployees(intColNum, intRowNum)
    'To Print in Table Format, no numbered Fields or Rows
    Debug.Print varEmployees(intColNum, intRowNum),
  Next
  Debug.Print vbCrLf
  'Debug.Print "******************************************"     'Column Format only
Next
 
rstEmployees.Close
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.

[sal21]

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

[ADezii]

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

[sal21]

@ADezii
please link of post#9 i dont see that(!?);-(