Retrieving Data From a DAO Recordset Using GetRows()

ADezii

One question which pops up frequently here at TheScripts is: 'How do I retrieve data from a Recordset once I've created it?' One very efficient, and not that often used approach, is the GetRows() Method of the Recordset Object. This Method varies slightly from DAO to ADO, so for purposes of this discussion, we'll be talking about DAO Recordsets. The ADO approach will be addressed in the following Tip. We'll be using a Query, consisting of 5 Fields, and based on the Employees Table of the sample Northwind Database, for both DAO and ADO discussions. The Recordset that we will be retrieving Rows from will be based on this Query.

The GetRows() Method copies Records from a Recordset and places them in a 2-dimensional Array. The first subscript identifies the Field, while the second identifies the Row. An outline of the GetRows() Method syntax is listed below:

Expand|Select|Wrap|Line Numbers

 
varArray = Recordset.GetRows(numberofrows)

    varArray - a Variant, 2-dimensional Array storing the returned data

    recordset - an Object Variable representing a Recordset

    numberofrows - a Variant indicating the number of Rows to retrieve

Special Considerations involving the GetRows() Method
1. If you request more Rows than are available, GetRows() returns only the number of available Rows.
2. The UBound() Function is used to determine how many Rows are actually retrieved, because the Array is sized to fit the number of the returned Rows.
3. Because GetRows() returns 'all' Fields of the Records, you may want to restrict the Fields returned in the Query.
4. After you call the GetRows() Method, the Current Record is positioned at the next, unread, Row,
5. The GetRows() Method will return fewer than the number of Rows requested in two cases: if the EOF has been reached, or if the Method tried to retrieve a Record that was deleted by another User.

Rather than going into a prolonged discussion of exactly how this Method works, I've decided to post a well documented code block. Hopefully, this code segment will illustrate the important aspects of this very useful Method. Should there be any questions whatsoever, please feel free to inquire or comment, and bring them up for discussion. I've also made the Test Database that I've used for this Tip available for download as an Attachment. Feel free to do whatever you like with it.

Expand|Select|Wrap|Line Numbers

 
Dim MyDB As DAO.Database

Dim rstEmployees As DAO.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 MyDB = CurrentDb

Set rstEmployees = MyDB.OpenRecordset("qryEmployees", dbOpenSnapshot)
 
'sometimes necessary for a valid Record Count

rstEmployees.MoveLast

rstEmployees.MoveFirst
 
'Let's retrieve ALL Rows in the rstEmployees Recordset

varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount)
 
'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
 
'Let's retrieve the first 6 Rows in the rstEmployees Recordset

'varEmployees = rstEmployees.GetRows(6)
 
'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

OUTPUT:

Expand|Select|Wrap|Line Numbers

 
Number of Rows Retrieved: 9
 
Number of Fields Retrieved: 5
 
Field 3 - Row 5: 908 W. Capital Way

Field 1 - Row 2: Callahan
 
Last Name     First Name    Address                     City          Region

---------------------------------------------------------------------------------------------

Buchanan      Steven        14 Garrett Hill             London        Null          
 
Callahan      Laura         4726 - 11th Ave. N.E.       Seattle       WA            
 
Davolio       Nancy         507 - 20th Ave. E., Apt. 2A Seattle       WA            
 
Dodsworth     Anne          7 Houndstooth Rd.           London        Null          
 
Fuller        Andrew        908 W. Capital Way          Tacoma        WA            
 
King          Robert        Edgeham Hollow, Winchester Way            London        Null          
 
Leverling     Janet         722 Moss Bay Blvd.          Kirkland      WA            
 
Peacock       Margaret      4110 Old Redmond Rd.        Redmond       WA            
 
Suyama        Michael       Coventry House, Miner Rd.   London        Null

jimatqsi

So would this be a good way to retrieve data from a crosstab query when you don't know for sure how many columns will be returned or what they will be named?

What are the reasons for using this method?

Thanks,
Jim

ADezii

Quote:

Originally Posted by jimatqsi

So would this be a good way to retrieve data from a crosstab query when you don't know for sure how many columns will be returned or what they will be named?

What are the reasons for using this method?

Thanks,
Jim

In an instant, GetRows() transfers the contents of a Recordset into a 2-Dimensional, Variant, Array for easy retrieval. To duplicate this functionality with traditional techniques would be much more laborious and time consuming.

Retrieving Data From a DAO Recordset Using GetRows()

re: Retrieving Data From a DAO Recordset Using GetRows()

re: Retrieving Data From a DAO Recordset Using GetRows()

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about