473,672 Members | 3,537 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

GetRows

Hello,
I'm having trouble reading from a table directly into an
array using Access 2000.

Dim db As Database
Dim rsTime As Recordset
Dim TimeArray As Variant

Set db = CurrentDb
Set rsTime = db.OpenRecordse t("Time Record")
' Only load array if records exist
If (rsTime.RecordC ount 0) Then
rsTime.MoveLast : rsTime.MoveFirs t
' The record count = 10
TimeArray = rsTime.GetRows
End If
It only appears to load the first record. I see only row 0
vResult = TimeArray(0,0) results in good data
vResult = TimeArray(0,1) gives me a "Subscript out of Range"
error

Any ideas would be appreciated.
Hank Reed

Jul 24 '06 #1
10 26302

Hank wrote:
Hello,
I'm having trouble reading from a table directly into an
array using Access 2000.

Dim db As Database
Dim rsTime As Recordset
Dim TimeArray As Variant

Set db = CurrentDb
Set rsTime = db.OpenRecordse t("Time Record")
' Only load array if records exist
If (rsTime.RecordC ount 0) Then
rsTime.MoveLast : rsTime.MoveFirs t
' The record count = 10
TimeArray = rsTime.GetRows
End If
It only appears to load the first record. I see only row 0
vResult = TimeArray(0,0) results in good data
vResult = TimeArray(0,1) gives me a "Subscript out of Range"
error

Any ideas would be appreciated.
I think you need to specify the number of rows you want returned from
your recordset. Try the following:

Dim db As Database
Dim rsTime As Recordset
Dim TimeArray As Variant

Set db = CurrentDb
Set rsTime = db.OpenRecordse t("Time Record")
' Only load array if records exist
If (rsTime.RecordC ount 0) Then
rsTime.MoveLast
' The record count = 10
TimeArray = rsTime.GetRows( rsTime.RecordCo unt)
End If

HTH,
Bruce

Jul 24 '06 #2
Bruce,

Perfect! So simple.
Apparently the other way would be to traverse the table getting
one record at a time.

Thanks for your time,
Hank

Jul 24 '06 #3
"Hank" <ha********@aol .comwrote in
news:11******** **************@ m79g2000cwm.goo glegroups.com:
I'm having trouble reading from a table directly into an
array using Access 2000.
Why would you bother doing that? A recordset is an array. Why not
just use the recordset to do what you want?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 24 '06 #4
David,

Thanks for your concern. I had seen the same caveat from Albert
Kallall when I was researching old threads.

My Time Table has over a million records. Typically, if I want to
make some calculations on one Job or one employee, I extract the
records of interest to a temporary table. Even with the overhead of
copying the data, the function runs a lot faster because of the smaller
dataset. If my approach is wrong after all these years, there is no
one I would rather hear comment on it than you.

I was planning to do some benchmarks with the array idea to
see if any efficiencies could be realized with that approach.

Your opinion on this is always welcome.

Hank Reed

Jul 25 '06 #5
Hank <ha********@aol .comwrote:
: Hello,
: I'm having trouble reading from a table directly into an
: array using Access 2000.

: Dim db As Database
: Dim rsTime As Recordset
: Dim TimeArray As Variant

: Set db = CurrentDb
: Set rsTime = db.OpenRecordse t("Time Record")
: ' Only load array if records exist
: If (rsTime.RecordC ount 0) Then
: rsTime.MoveLast : rsTime.MoveFirs t
: ' The record count = 10
: TimeArray = rsTime.GetRows
/\ /\
|| ||
I think that here you've created a loop that looks at one
record at a time and each time overwrites the TimeArray
with the contents of that record only: the last record
it overwrites with is the first record in your recordset
since you're reading last to first

: End If
: It only appears to load the first record. I see only row 0
: vResult = TimeArray(0,0) results in good data
: vResult = TimeArray(0,1) gives me a "Subscript out of Range"
: error

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

This is a direct quote from the Microsoft online help files:
<BEGIN MS GETROWS METHOD DEFINITION EXTRACT>******* *************** *********

GetRows Method
Retrieves multiple records of a Recordset object into an array.

Syntax
array = recordset.GetRo ws( Rows, Start, Fields )
Return Value
Returns a Variant whose value is a two-dimensional array.

Parameters
Rows
Optional. A GetRowsOptionEn um value that indicates the number of
records to retrieve. The default is adGetRowsRest.

<snip>

Use the GetRows method to copy records from a Recordset into a
two-dimensional array. The first subscript identifies the field and
the second identifies the record number. The array variable is
automatically dimensioned to the correct size when the GetRows method
returns the data.

If you do not specify a value for the Rows argument, the GetRows
method automatically retrieves all the records in the Recordset
object. If you request more records than are available, GetRows
returns only the number of available records.

<snip>

<END MS GETROWS METHOD DEFINITION EXTRACT>******* *************** ***********
So the default action of the Getrows method is to extract all records
from the recordset.

I've been using code given to me in this newsgroup by Lyle Fairfield
to get an array: I like it because it bypasses the need to define an
intermediate recordset:

TimeArray() = CurrentProject. Connection.Exec ute(strSQL).Get Rows()

I guess that for your purpose strSQL would simply be

"SELECT * from [Time Record]"

(CurrentProject .Connection.Exe cute(strSQL) is a recordset)
*************** ***********
Question for David Fenton:
Does your objection to filling array from recordset
apply to this method too?

--thelma
: Any ideas would be appreciated.
: Hank Reed
Jul 25 '06 #6
Thelma Lubkin wrote:
GetRows Method
Retrieves multiple records of a Recordset object into an array.

Syntax
array = recordset.GetRo ws( Rows, Start, Fields )
Return Value
Returns a Variant whose value is a two-dimensional array.

Parameters
Rows
Optional. A GetRowsOptionEn um value that indicates the number of
records to retrieve. The default is adGetRowsRest.
Did you notice that there is a DAO GetRows and an ADO GetRows. You are
quoting (and using, I think) ADO, but I think Hank is using DAO.

I think the NumRows Parameter in DAO GetRows(NumRows ) is not documented
well. The Help file does not indicate that NumRows is optional, but the
Object Browser does, but not the default value. Expperience shows the
default value in DAO is one, while, as you have pointed out, in ADO its
the rest.

I don't know about whether it's worthwhile to use GetRows in VBA. As
David points out, the Recordset can be used in somewhat the same way a
VBA array can. My experience is that VBA Array manipulation is faster
than Recordset manipulation, so, if one plans to do a lot of
manipulation, setting the Array may be worth it. It's also nice to know
that one's data is secure. As one is not working with your Recordset,
one can be sure one is not doing anything bad to the data, which may
trickle down to be stored in some unforeseen way.

Regardless when it comes to languages where array manipulation is many
times more advanced than VBA, the array can be very worthwhile and
millions of times faster than Recordset manipulation. Here we have
methods such as concat, join, pop, push, reverse, shift, slice, sort,
splice and unshift that make Arrays incredibly powerful. One such not
quite a language is javascript.

Jul 25 '06 #7
"Lyle Fairfield" <ly***********@ aim.comwrote
One such not quite a language is javascript.
Lyle, your pithy, pertinent comments often make me smile -- you just did it
again!

Cheers,

Larry
Jul 25 '06 #8
Lyle,

I had to look up the meaning of pithy, but if you take the
definition of concise and to the point then Larry is right on with his
comment. I believe your statements are on target. It's a good
thing to realize that some ideas are just experiments for
experiment's sake and do not need to show ground breaking results.

I agree that, depending on the task, arrays can be quite
efficient.

Paraphrasing Thomas Edison. Well I didn't get the light
bulb to light but now I know 100 things that won't work.

Thanks to everyone,
Hank Reed

Larry Linson wrote:
"Lyle Fairfield" <ly***********@ aim.comwrote
One such not quite a language is javascript.

Lyle, your pithy, pertinent comments often make me smile -- you just did it
again!

Cheers,

Larry
Jul 25 '06 #9
"Hank" <ha********@aol .comwrote in
news:11******** **************@ h48g2000cwc.goo glegroups.com:
My Time Table has over a million records. Typically, if I
want to
make some calculations on one Job or one employee, I extract the
records of interest to a temporary table. Even with the overhead
of copying the data, the function runs a lot faster because of the
smaller dataset. If my approach is wrong after all these years,
there is no one I would rather hear comment on it than you.

I was planning to do some benchmarks with the array idea
to
see if any efficiencies could be realized with that approach.
I can't see how the overhead of loading 1 million records into an
array could possibly result in faster calculations than you'd get on
a subset of records in a temp table.

Is the temp table slow from an end user's point of view?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 25 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
8172
by: Croney69 | last post by:
I am getting information out of a table to place into to an Array. rs=DataConn.Execute(strSQL) At this point I place it into the array objArray=rs.GetRows() But how do I handle things if the rs came back blank?
8
2475
by: Moshe | last post by:
I'm facing an odd behavior in using the GetRows Method. I'm not sure what's causing it because it has been working fine until now. I have a sproc that returns 1 row. I display the RS info on the page using a DO-WHILE loop. All fields have values. I then rerun the same sproc, but this time extract the values into an array using getrows. I loop through the array and display the content of each element on screen. However, from the 14...
4
5858
by: Harag | last post by:
Hi All I currently thinking of converting from my little knowledge of VBscript to jScript ASP. With this in mind I'm looking at my current code to see how it will convert over to Jscript. One thing I have spotted that I can't think of a way round is the ADO recordset.GetRows command.
11
3512
by: Laphan | last post by:
Hi All I'm using .getRows() with a local var array instead of doing a recursive loop so that I'm being a good ASP newvbie and closing my object i/o's (the recordset in this case) as quick as possible. My problem is that I can't seem to use this to complete good effect because the IsArray statement doesn't seem to work with a local var array that has or has not been populated with the .getRows() property.
9
3657
by: bajopalabra | last post by:
hi session("myVar") = rs.getRows( ) don't work when number of records is greater than 10 does anybody know WHY ??? is it a Session object limitation ??? thanks
3
3104
by: colm1974 | last post by:
Hi, I hope somebody can help. I have been trying to use the GetRows method but it only seems to work when there are no constants assigned. aGetElement = rs_zgl_element.GetRows() This works and I can select any array element I need from aGetElement. I can use Lbound, Ubound etc etc. In this case the recordset has in effect 2 columns and 197 rows. Column names are converted (int), name (varchar). aGetElement =...
1
2739
by: FrozenDude | last post by:
Help! I'm a VBA newbie and am having trouble getting the array values retrieved from getRows into a series of individual variables. Here is the query: "Select parcelID, DueDate from tablename" I have the array populated with getRows and have the number of returned rows. I need to loop through the array - somehow - and assign the tow field to variavles numParcelID and newDuedate respectively so I can use
9
35469
ADezii
by: ADezii | last post by:
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...
3
43481
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 Arguments, is a little more complex, and of course, the syntax is different in creating the...
0
8504
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8419
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8846
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8643
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 most users, this new feature is actually very convenient. If you want to control the update process,...
0
8697
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6255
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5720
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 into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4242
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
1837
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.