By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,334 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Retrieving Data From a DAO Recordset Using GetRows()

ADezii
Expert 5K+
P: 8,597
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
  1. varArray = Recordset.GetRows(numberofrows)
  2.     varArray - a Variant, 2-dimensional Array storing the returned data
  3.     recordset - an Object Variable representing a Recordset
  4.     numberofrows - a Variant indicating the number of Rows to retrieve
  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.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstEmployees As DAO.Recordset
  3. Dim varEmployees As Variant
  4. Dim intRowNum As Integer
  5. Dim intColNum As Integer
  6.  
  7. 'Make up of qryEmployees (5 Fields/9 Records) based on the
  8. 'sample Northwind.mdb Database
  9.   '[LastName] - Ascending
  10.   '[FirstName] - Ascending
  11.   '[Address]
  12.   '[City]
  13.   '[Region]
  14.  
  15. Set MyDB = CurrentDb
  16. Set rstEmployees = MyDB.OpenRecordset("qryEmployees", dbOpenSnapshot)
  17.  
  18. 'sometimes necessary for a valid Record Count
  19. rstEmployees.MoveLast
  20. rstEmployees.MoveFirst
  21.  
  22. 'Let's retrieve ALL Rows in the rstEmployees Recordset
  23. varEmployees = rstEmployees.GetRows(rstEmployees.RecordCount)
  24.  
  25. 'If fewer than the desired number of Reows were returned
  26. If rstEmployees.RecordCount > UBound(varEmployees, 2) + 1 Then
  27.   MsgBox "Fewer Rows were returned than those requested"
  28. End If
  29.  
  30. 'Let's retrieve the first 6 Rows in the rstEmployees Recordset
  31. 'varEmployees = rstEmployees.GetRows(6)
  32.  
  33. '1st Row is the 0 Element of the Array, so we need the +1
  34. '2nd Subscript (2) identifies the Row Number
  35. Debug.Print "Number of Rows Retrieved: " & UBound(varEmployees, 2) + 1
  36.  
  37. Debug.Print
  38.  
  39. '1st Field is the 0 Element of the Array, so we need the +1
  40. '1st Subscript (1) identifies the Field
  41. Debug.Print "Number of Fields Retrieved: " & UBound(varEmployees, 1) + 1
  42.  
  43. Debug.Print
  44.  
  45. 'Let's retrieve the value of the 3rd Field ([Address]) in Row 5
  46. Debug.Print "Field 3 - Row 5: " & varEmployees(2, 4)
  47. 'Let's retrieve the value of the 1st Field ([LastName]) in Row 2
  48. Debug.Print "Field 1 - Row 2: " & varEmployees(0, 1)
  49.  
  50. Debug.Print
  51.  
  52. 'Debug.Print "******************************************"       'Column Format only
  53. Debug.Print "Last Name", "First Name", "Address", , "City", "Region"
  54. Debug.Print "---------------------------------------------------------------------------------------------"
  55. For intRowNum = 0 To UBound(varEmployees, 2)        'Loop thru each Row
  56.   For intColNum = 0 To UBound(varEmployees, 1)      'Loop thru each Column
  57.     'To Print Fields in Column Format with numbered Field and Row
  58.     'Debug.Print "Record#:" & intRowNum + 1 & "/Field#:" & intColNum + 1 & " ==> " & _
  59.                  'varEmployees(intColNum, intRowNum)
  60.     'To Print in Table Format, no numbered Fields or Rows
  61.     Debug.Print varEmployees(intColNum, intRowNum),
  62.   Next
  63.   Debug.Print vbCrLf
  64.   'Debug.Print "******************************************"     'Column Format only
  65. Next
  66.  
  67. rstEmployees.Close
  68. Set rstEmployees = Nothing
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Number of Rows Retrieved: 9
  2.  
  3. Number of Fields Retrieved: 5
  4.  
  5. Field 3 - Row 5: 908 W. Capital Way
  6. Field 1 - Row 2: Callahan
  7.  
  8. Last Name     First Name    Address                     City          Region
  9. ---------------------------------------------------------------------------------------------
  10. Buchanan      Steven        14 Garrett Hill             London        Null          
  11.  
  12. Callahan      Laura         4726 - 11th Ave. N.E.       Seattle       WA            
  13.  
  14. Davolio       Nancy         507 - 20th Ave. E., Apt. 2A Seattle       WA            
  15.  
  16. Dodsworth     Anne          7 Houndstooth Rd.           London        Null          
  17.  
  18. Fuller        Andrew        908 W. Capital Way          Tacoma        WA            
  19.  
  20. King          Robert        Edgeham Hollow, Winchester Way            London        Null          
  21.  
  22. Leverling     Janet         722 Moss Bay Blvd.          Kirkland      WA            
  23.  
  24. Peacock       Margaret      4110 Old Redmond Rd.        Redmond       WA            
  25.  
  26. Suyama        Michael       Coventry House, Miner Rd.   London        Null   
Apr 1 '08 #1
Share this Article
Share on Google+
9 Comments


Expert 100+
P: 1,221
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
Apr 3 '08 #2

ADezii
Expert 5K+
P: 8,597
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.
Apr 3 '08 #3

Nauticalgent
P: 88
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!
May 30 '17 #4

ADezii
Expert 5K+
P: 8,597
  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.
May 30 '17 #5

Nauticalgent
P: 88
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("username") 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 responsibilities 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:
Expand|Select|Wrap|Line Numbers
  1. Public Function GetJobTitle(ByRef frm as Form)
  2.      Dim strResp as String
  3.      Dim strUser as String
  4.      Dim intResp as Long
  5.      Dim ctlCurr as Control
  6.  
  7.      strUser = fosUserName
  8.      strResp = Nz(Dlookup("[Responsibilities]","tblUsers", "[User Name] = '" & strUser & "'"
  9.  
  10.      Select Case strResp
  11.           Case "DBA"
  12.                intResp = 0
  13.           Case "Ops Center"
  14.                intResp = 1
  15.           Case "Tech Director"
  16.                intResp = 2
  17.           Case "Tech Supervisor"
  18.                intResp = 3
  19.           Case "Tech"
  20.                intResp = 4
  21.           Case Else
  22.                intResp = 999
  23.      End Select
  24.  
  25.      For Each ctlCurr in frm.Controls
  26.           Select Case ctlCurr.Tag
  27.                Case Is < intResp
  28.                    ctlCurr.Visible = False
  29.                Case Else
  30.                    ctlCurr.Visible = True
  31.           End Select
  32.      Next ctlCurr
  33.  
  34. End Function
  35.  
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.
May 31 '17 #6

ADezii
Expert 5K+
P: 8,597
  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.
    Expand|Select|Wrap|Line Numbers
    1. Public Type UserInfo
    2.   UName As String
    3.   EMail As String
    4.   JobTitle As String
    5.   LogDate As Date
    6. End Type
    7.  
    8. 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.
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database
    2. Dim rst As DAO.Recordset
    3.  
    4. Set MyDB = CurrentDb
    5. Set rst = MyDB.OpenRecordset("SELECT * FROM Employees WHERE [EmployeeID]=1", dbOpenSnapshot)    '1 Record
    6.  
    7. 'Fill the Enumeration/User Defined Type
    8. With rst
    9.   uInfo.UName = ![LastName]
    10.   uInfo.EMail = ![email]
    11.   uInfo.JobTitle = ![Title]
    12.   uInfo.LogDate = ![HireDate]
    13. End With
    14.  
    15. rst.Close
    16. Set rst = Nothing
  4. Now, from anywhere within your App, you can refer to/access the Structure's Elements as in:
    Expand|Select|Wrap|Line Numbers
    1. Debug.print "The Current User Info is: " uInfo.Name, uInfo.EMail, uInfo.JobTitle, uInfo.LogDate
  5. which will produce:
    Expand|Select|Wrap|Line Numbers
    1. 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:
    Expand|Select|Wrap|Line Numbers
    1. strResp = Nz(Dlookup("[Responsibilities]","tblUsers", "[User Name] = '" & uInfo.Name & "'"
    2.  
  7. This approach definitely avoids multiple DLookups and should suite your needs.
  8. Let us knkow how you make out.
May 31 '17 #7

Nauticalgent
P: 88
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!
May 31 '17 #8

ADezii
Expert 5K+
P: 8,597
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.
May 31 '17 #9

Nauticalgent
P: 88
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!
Aug 14 '18 #10