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: -
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
- If you request more Rows than are available, GetRows() returns only the number of available Rows.
- 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.
- Because GetRows() returns 'all' Fields of the Records, you may want to restrict the Fields returned in the Query.
- After you call the GetRows() Method, the Current Record is positioned at the next, unread, Row,
- 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. -
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: -
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
9  35288 
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
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.
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!
- What is the structure and nature of your Credentials Table: number of Fields and their Data Types, number of Records, sample Data, etc.
- What is the extent to which you use DLookup(): how many times, where and when referenced, etc.
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: -
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.
- 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.
- 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
- 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
- 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
- which will produce:
- Current User Info is: Davolio Me@aol.com Sales Representative 5/1/1992
- Another example of how this Logic can be used would be:
- strResp = Nz(Dlookup("[Responsibilities]","tblUsers", "[User Name] = '" & uInfo.Name & "'"
-
- This approach definitely avoids multiple DLookups and should suite your needs.
- Let us knkow how you make out.
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!
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.
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!
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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),...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
