recordset to Array

[Brian1960]

I have some code as follows: to insert a recordset into an array and then loop through it.

sSQL = "SELECT imagename FROM tblproducts "
Set rstimage = CurrentDb.OpenRecordset(sSQL)

If Not rstimage.EOF Then arData = rstimage.GetRows()
   intCount = rstimage.RecordCount
   iRecFirst = LBound(arData, 2)
   iRecLast = UBound(arData, 2)
rstimage.Close
Set rstimage = Nothing

The problem I have, is that intcount = 501 so I would expect iRecLast to be 500 (or thereabouts) but it is always 0.
I then use, this to loop through the array.

  For i = iRecFirst To iRecLast
               If Left(strBaseName, 13) = arData(0, i) Then
                    boofound = True
                End If
            Next i

This loops 1 time and then arData(0,1) returns <subscript out of range>
I have been at this for hours and I can't get the recordset into the array,

Any ideas?

Thanks
Brian

 

[pono1]

Using the Ubound function on a 2-dimensional array created with Getrows will return, as you discovered, the number of fields queried rather than the number of records returned...

Ubound(My2DimensionalArray) = Number of fields in recordset

To get an idea of the array's structure after creating it from a recordset: Say you query a table for two fields -- and the table has three records total...

MyArray(0)
   MyArray(0,0)	<-- Field 1, Record 1 value
   MyArray(0,1)	<-- Field 1, Record 2 value
   MyArray(0,2)	<-- Field 1, Record 3 value
MyArray(1)
   MyArray(1,0) <-- Field 2, Record 1 value
   MyArray(1,1) <-- Field 2, Record 2 value
   MyArray(1,2) <-- Field 2, Record 3 value

You may want to type the Stop keyword in your code after calling Getrows and then open the Locals window -- when the code stops -- to see your array's structure...

Regards,
Tim

 

[Pat Hartman]

Since a recordset IS an array, why would you loop through that array only to add records to another array so you can loop through that?

 

[Brian1960]

Recodset and arrays

Since a recordset IS an array, why would you loop through that array only to add records to another array so you can loop through that?

A very fair point. Two reasons. 1. I am about to build a shopping cart system to replace an existing one and it will use arrays (asp.net) and 2. I can't think of this one.
I think I get the UBound thing now... but then surely this would work?

  For i = 0 To intCount
         If Left(strBaseName, 13) = arData(0, i) Then
                    boofound = True
        End If
  Next i

but it doesn't.
It works for arData(0,0) = the first record
but arData(0,1) = <subscript out of range>

Not what was expected.

Thanks for the help,

Brian

 

[Jon K]

   sSQL = "SELECT imagename FROM tblproducts "
   Set rstimage = CurrentDb.OpenRecordset(sSQL)

   [b]rstimage.MoveLast[/b] 
   [b]rstimage.MoveFirst[/b]

If Not rstimage.EOF Then arData = rstimage.GetRows([b]rstimage.RecordCount[/b])

To use the GetRows method of a DAO recordset, you must use GetRows(NumOfRows) to tell Access how many rows to return.

To get the correct RecordCount for GetRows(), the recordset has to MoveLast first.

To position at the starting record for GetRows(), the recordset has to MoveFirst first.


The number of rows that you can retrieve is constrained by the amount of available memory.
.

 

[Brian1960]

Arrany Syntax

To use the GetRows method of a DAO recordset, you must use GetRows(NumOfRows) to tell Access how many rows to return.
[color=white].[/color][/QUOTE]
Are you sure? Actually that is a polite way of saying I a am sure you are wrong.:) 
vararray=objRecordset.GetRows(rows,start,fields) is the syntax with the paramaters all optional.

I agree that it is just possible that it could be memory but since it only returns 500 records and I have 1Gb RAM it is unlikely. I would also get a virtual memory notice appearing.

Could it be the way the recodset is created?

B

 

[Jon K]

Since you used "Set rstimage = CurrentDb.OpenRecordset(sSQL)" in your code, I presume you are using a DAO recordset.

I'm sure I'm right, at lest with DAO 3.6 in Access.


The following is from Access' help file for DAO 3.6 object library:-
-----------------------------------------------
GetRows Method


Retrieves multiple rows from a Recordset object.

Syntax

varArray = recordset.GetRows (numrows)

The GetRows method syntax has the following parts.

varArray : A Variant that stores the returned data.
recordset : An object variable that represents a Recordset object.
numrows : A Variant that is equal to the number of rows to retrieve.
------------------------------------------------
The remark about amount of memory is also from the above help file.


You can try the command button on the form in the attached Access database.

.

 

[Brian1960]

Thanks,
All very odd as in ADO 2.5 the same code has it as optional.
I'll certainly try to do it with the number of rows included but i might be better to make sure it uses ADO so that it is less cumbersome, especially as I need to write this eventually into asp.net.
Goodeness know how I force it to use ADO and not DAO. Possibly in seting the recorset as an ADOObject.
I'll post the solution when I get it.

B