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()
Expand|Select|Wrap|Line Numbers
- 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
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.