473,553 Members | 2,953 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Retrieving Data From a DAO Recordset Using GetRows()

ADezii
8,834 Recognized Expert Expert
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
9 35375
jimatqsi
1,274 Recognized Expert Top Contributor
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
8,834 Recognized Expert Expert
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
100 New Member
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
8,834 Recognized Expert Expert
  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
100 New Member
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:
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
8,834 Recognized Expert Expert
  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
100 New Member
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
8,834 Recognized Expert Expert
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
100 New Member
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

Sign in to post your reply or Sign up for a free account.

Similar topics

2
1766
by: John M | last post by:
Hi, Having struggled to master (or at least get to use) recordsets, and so retrieve data without using a form, I'm stuck at a point which I feel ought to be easy. MsgBox Rs.Myfield (with brackets, without, with ! or .) will produce a result, so my recordset (rs) is fine. Now I want to choose the field through a function, so that the...
5
2223
by: aniket_sp | last post by:
i am using a data adapter and a dataset for filling and retrieving data into .mdb database. following is the code..... for the form load event Dim dc(0) As DataColumn Try If OleDbConnection1.State = ConnectionState.Closed Then OleDbConnection1.Open() Else
1
5688
by: stjulian | last post by:
If inside a stored procedure, there a SELECT statement to return a recordset and another SELECT to set the value of an output parameter (as in SELECT @OutValue = Name FROM table WHERE pkid=5), would 2 execute statements be needed to return the OUTPUT parameter? Like this? adocmd.CommandTimeout = 120 adocmd.ActiveConnection = conn
0
2042
by: Andy | last post by:
Hi All. I'm working for a company that has set out a guideline for retrieving data from a database. Nobody can explain to me the reason for the following. When retrieving a set of records from database to a VB.net app, they retrieve the database fields as a record set eg. "select name, suburb from myTable"
3
3644
by: Jakob Petersen | last post by:
Hi, I need to increase the speed when retrieving data from a hosted SQL Server into VBA. I'm using simple SELECT statements. How important is the speed of my Internet connection? (I have 4mbits) Should I index my tables or use Stored Procedures? Or is there a kind of "flush" function or readonly function or... Or is it simply a...
0
1514
by: DC01 | last post by:
I have added a new measure successfully into the normal cube. I then add it to the virtual cube and reprocess all cubes. I can browse the normal cube successfully. Then when I try and browse the virtual cube it says 'Retrieving Data' but then thats it, it just hangs like that. I am using AS2000. The other problem I have is that the data...
4
18800
by: smartin | last post by:
Hi, I'm having problem retrieving data from an SQL stored procedure. I tried debugging but it wont give a the reason for the error. it just throws an exception after executing cmd.ExecuteNonQuery without any details. Can anyone please help me.. Im stuck on it since 2 days Thanks Stored Procedure
3
43421
ADezii
by: ADezii | last post by:
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...
3
3930
by: Executable | last post by:
Hi everyone, I have a question. I am creating a dynamic report from a table and need to fetch all the data (values and column names) into an array so that I can manipulate the data. I know there is a way to get the values in the recordset using GetRows() but that Only gives me the values, I also need the Field names of the columns. Does...
0
8007
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7526
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6109
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5409
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5133
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3537
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1990
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1106
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
808
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.