GetRows

[Hank]

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.OpenRecordset("Time Record")
' Only load array if records exist
If (rsTime.RecordCount 0) Then
rsTime.MoveLast: rsTime.MoveFirst
' 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

[deluxeinformation]

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.OpenRecordset("Time Record")
' Only load array if records exist
If (rsTime.RecordCount 0) Then
rsTime.MoveLast: rsTime.MoveFirst
' 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.OpenRecordset("Time Record")
' Only load array if records exist
If (rsTime.RecordCount 0) Then
rsTime.MoveLast
' The record count = 10
TimeArray = rsTime.GetRows(rsTime.RecordCount)
End If

HTH,
Bruce

[Hank]

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

[David W. Fenton]

"Hank" <ha********@aol.comwrote in
news:11**********************@m79g2000cwm.googlegr oups.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/

[Hank]

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

[Thelma Lubkin]

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.OpenRecordset("Time Record")
: ' Only load array if records exist
: If (rsTime.RecordCount 0) Then
: rsTime.MoveLast: rsTime.MoveFirst
: ' 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.GetRows( Rows, Start, Fields )
Return Value
Returns a Variant whose value is a two-dimensional array.

Parameters
Rows
Optional. A GetRowsOptionEnum 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.Execute(strSQL).GetRows( )

I guess that for your purpose strSQL would simply be

"SELECT * from [Time Record]"

(CurrentProject.Connection.Execute(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

[Lyle Fairfield]

Thelma Lubkin wrote:

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

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

Parameters
Rows
Optional. A GetRowsOptionEnum 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.

[Larry Linson]

"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

[Hank]

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

[David W. Fenton]

"Hank" <ha********@aol.comwrote in
news:11**********************@h48g2000cwc.googlegr oups.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/

[David W. Fenton]

Thelma Lubkin <th****@alpha2.csd.uwm.eduwrote in
news:ea**********@uwm.edu:

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.Execute(strSQL).GetRows( )

I guess that for your purpose strSQL would simply be

"SELECT * from [Time Record]"

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

Well, yes. Why populate an array when a recordset is an array that
you can address with user-friendly names (i.e., field names, instead
of index numbers).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/