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:
[CODE=text]
varArray = Recordset.GetRo ws(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[/CODE]

1. 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.
[CODE=vb]
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.OpenRecord set("qryEmploye es", dbOpenSnapshot)

'sometimes necessary for a valid Record Count
rstEmployees.Mo veLast
rstEmployees.Mo veFirst

'Let's retrieve ALL Rows in the rstEmployees Recordset
varEmployees = rstEmployees.Ge tRows(rstEmploy ees.RecordCount )

'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

'Let's retrieve the first 6 Rows in the rstEmployees Recordset
'varEmployees = rstEmployees.Ge tRows(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(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]

OUTPUT:
[CODE=text]
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 [/CODE]

[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]

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.

[Nauticalgent]

I realize I am a little late to the party here, hopefully the OP is still around!

I have an application that requires me to use a User's credentials (local table) to determine what they can have access to, controls that are enabled and so on and so forth.

I have been doing this with DLookUps but it seems very inefficient and even the great Allen Browne has mentioned in a few of his articles how much he dislikes Domain functions.

I have thought about loading an array with their info so that I could recall them as needed. My question is: Would loading a two-dimensional array with about 3-5 fields of data at start-up and keeping it in memory be as much a hit on performance as using Dlookups or is the GetRows() function what I am looking for?

Thanks in advance!

[ADezii]

1. What is the structure and nature of your Credentials Table: number of Fields and their Data Types, number of Records, sample Data, etc.
2. What is the extent to which you use DLookup(): how many times, where and when referenced, etc.

[Nauticalgent]

ADezii,

Thanks for the quick reply, apologies for taking so long to respond.

I use a function that leverages a windows API to determine the Login. The Function is called fosUserName and it is the equivalent to using the environ("userna me") function.

I would use this function in a query that would return a Record Set with what should be 1 record and it is that record that I would use the GetRows() function to load the Array.

The RS would have 4 fields that I would reference in the application. These fields are the UserName, e-mail, JobTitle and LoginDate.

As it stands now, the only fields I am using is the UserName and JobTitle. This is where it gets funky:

I have a Public Function that determines the user's responsibilitie s and assigns it a numeric value. That value is then used in some on my forms OnOpen event to determine which fields are enabled and/or Visible. This is done with the Tag property of the form's controls.

Code:

Public Function GetJobTitle(ByRef frm as Form)
     Dim strResp as String
     Dim strUser as String
     Dim intResp as Long
     Dim ctlCurr as Control

     strUser = fosUserName
     strResp = Nz(Dlookup("[Responsibilities]","tblUsers", "[User Name] = '" & strUser & "'"

     Select Case strResp
          Case "DBA"
               intResp = 0
          Case "Ops Center"
               intResp = 1
          Case "Tech Director"
               intResp = 2
          Case "Tech Supervisor"
               intResp = 3
          Case "Tech"
               intResp = 4
          Case Else
               intResp = 999
     End Select

     For Each ctlCurr in frm.Controls
          Select Case ctlCurr.Tag
               Case Is < intResp
                   ctlCurr.Visible = False
               Case Else
                   ctlCurr.Visible = True
          End Select
     Next ctlCurr

End Function

This code is on a Classified system so I was not able to cut-n-paste and had to "hand-jam" it. If there are any typos, that is the reason.

I realize that there may be a better way to achieve what I am doing so please don't be shy with the criticism and suggestions.

[ADezii]

1. I am not sure if I would use an Array in this particular case, but I do feel as though I have a much better solution that will eliminate the repeated Lookups in your Code. The solution that I am proposing is a User Defined Type or Enumeration. You Declare this Structure as Public and initialize it only once. From this point on, you can refer to its Components anytime during the life of your Application. I'll work you through the steps now.
2. Declare the User Defined Type as well as a Variable to represent an Instance of that Type as Public in a Standard Code Module.
   

   Public Type UserInfo
     UName As String
     EMail As String
     JobTitle As String
     LogDate As Date
   End Type
   
   Public uInfo As UserInfo       'Declare a GLOBAL Instance of the User Defined type

3. Simulate the single Record Recordset (Northwind) as you previously indicated and populate the Structure with it's Fields.
   

   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
   
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset("SELECT * FROM Employees WHERE [EmployeeID]=1", dbOpenSnapshot)    '1 Record
   
   'Fill the Enumeration/User Defined Type
   With rst
     uInfo.UName = ![LastName]
     uInfo.EMail = ![EMail]
     uInfo.JobTitle = ![Title]
     uInfo.LogDate = ![HireDate]
   End With
   
   rst.Close
   Set rst = Nothing

4. Now, from anywhere within your App, you can refer to/access the Structure's Elements as in:
   

   Debug.print "The Current User Info is: " uInfo.Name, uInfo.EMail, uInfo.JobTitle, uInfo.LogDate

5. which will produce:
   

   Current User Info is: Davolio             Me@aol.com    Sales Representative        5/1/1992

6. Another example of how this Logic can be used would be:
   

   strResp = Nz(Dlookup("[Responsibilities]","tblUsers", "[User Name] = '" & uInfo.Name & "'"

7. This approach definitely avoids multiple DLookups and should suite your needs.
8. Let us knkow how you make out.

[Nauticalgent]

Dude!

Before I found the GetRows() Function I was looking into using the Type structures but wasn't sure if it was what I needed. In other words, I wasn't sure if a collection of variables would be more efficient that an Array - it was this research that led me to this thread.

Having you suggest it makes me more confident that it is the way to go. Supplying the code makes it easy.

As a side note, I originally was contemplating a Class Module to do this but it seemed like I was driving a finishing nail with a sledge-hammer.


Very impressive, Adezii and I cant thank you enough!

[ADezii]

You are quite welcome. Still not 100% sure that this is the best approach, but I do believe that it is intuitive and easily maintained.

[Nauticalgent]

Felt like I should re-visit this. I came across something I did about two years ago and long story short, I used about 17 DLookUps to extract the info I needed.

I thought to myself "Self," (that's what I call myself) "there has GOT to be a more efficient way to do this. Didn't I read somewhere about two-dimensional Arrays and such. Some kinda function called GetSome or GetStuffed..."

And then I remembered this thread. Thanks to your explanation and code I was able to facilitate this with ease.

Thanks again for the initial answer (Type Variables) and this one too!