[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]
[CODE EXAMPLE]
[CODE=vb]
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 = "qryEmploye es"
.ActiveConnecti on = CurrentProject. Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimisti c
.Open
.MoveLast
.MoveFirst
End With
'Let's retrieve ALL Rows in the rstEmployees Recordset
varEmployees = rstEmployees.Ge tRows(rstEmploy ees.RecordCount )
'Demonstration of the Fields Parameter of GetRows()
'Let's retrieve only the LastName Field in the rstEmployees Recordset
'varEmployees = rstEmployees.Ge tRows(rstEmploy ees.RecordCount , , "LastName")
'Let's retrieve only the 3rd Field ([Address]) in the rstEmployees Recordset
'varEmployees = rstEmployees.Ge tRows(rstEmploy ees.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.Ge tRows(rstEmploy ees.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.Ge tRows(rstEmploy ees.RecordCount , , avarFieldNames)
'************** *************** *************** *************** ******
'Demonstration of the Bookmark Parameter of GetRows()
'Retrieve only those Records from thew Current Bookmark on
'Dim varBkMrk As Variant
'rstEmployees.F ind "[LastName]='King'"
'varBkMrk = rstEmployees.Bo okmark
'varEmployees = rstEmployees.Ge tRows(rstEmploy ees.RecordCount , varBkMrk)
'************** *************** *************** *************** ******
'Let's retrieve the first 6 Rows in the rstEmployees Recordset
'varEmployees = rstEmployees.Ge tRows(6)
'If fewer than the desired number of Reows were returned
If rstEmployees.Re cordCount > UBound(varEmplo yees, 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(varEmplo yees, 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(varEmplo yees, 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(varEmplo yees, 2) 'Loop thru each Row
For intColNum = 0 To UBound(varEmplo yees, 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(i ntColNum, intRowNum)
'To Print in Table Format, no numbered Fields or Rows
Debug.Print varEmployees(in tColNum, intRowNum),
Next
Debug.Print vbCrLf
'Debug.Print "************** *************** *************" 'Column Format only
Next
rstEmployees.Cl ose
Set rstEmployees = Nothing[/CODE]
[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.
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]
[CODE=vb]
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 = "qryEmploye es"
.ActiveConnecti on = CurrentProject. Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimisti c
.Open
.MoveLast
.MoveFirst
End With
'Let's retrieve ALL Rows in the rstEmployees Recordset
varEmployees = rstEmployees.Ge tRows(rstEmploy ees.RecordCount )
'Demonstration of the Fields Parameter of GetRows()
'Let's retrieve only the LastName Field in the rstEmployees Recordset
'varEmployees = rstEmployees.Ge tRows(rstEmploy ees.RecordCount , , "LastName")
'Let's retrieve only the 3rd Field ([Address]) in the rstEmployees Recordset
'varEmployees = rstEmployees.Ge tRows(rstEmploy ees.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.Ge tRows(rstEmploy ees.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.Ge tRows(rstEmploy ees.RecordCount , , avarFieldNames)
'************** *************** *************** *************** ******
'Demonstration of the Bookmark Parameter of GetRows()
'Retrieve only those Records from thew Current Bookmark on
'Dim varBkMrk As Variant
'rstEmployees.F ind "[LastName]='King'"
'varBkMrk = rstEmployees.Bo okmark
'varEmployees = rstEmployees.Ge tRows(rstEmploy ees.RecordCount , varBkMrk)
'************** *************** *************** *************** ******
'Let's retrieve the first 6 Rows in the rstEmployees Recordset
'varEmployees = rstEmployees.Ge tRows(6)
'If fewer than the desired number of Reows were returned
If rstEmployees.Re cordCount > UBound(varEmplo yees, 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(varEmplo yees, 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(varEmplo yees, 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(varEmplo yees, 2) 'Loop thru each Row
For intColNum = 0 To UBound(varEmplo yees, 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(i ntColNum, intRowNum)
'To Print in Table Format, no numbered Fields or Rows
Debug.Print varEmployees(in tColNum, intRowNum),
Next
Debug.Print vbCrLf
'Debug.Print "************** *************** *************" 'Column Format only
Next
rstEmployees.Cl ose
Set rstEmployees = Nothing[/CODE]
[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.
Comment