Link to home
Start Free TrialLog in
Avatar of Robert Berke
Robert BerkeFlag for United States of America

asked on

how to move recordset into a vba array (similar to Excel code: varMyArray = myRange)

In excel I often grab a range of cells and put them into a matrix for futher use.

In Access, I use a macro like the attached UDF.  
But, it occured to me that there might be an easier way and a better way to do it.

Does anybody have any comments?

Function RsToArray(rs As DAO.Recordset)
rs.MoveFirst
dim1ubound = rs.RecordCount
dim2ubound = rs.Fields.Count
Dim myArray()
ReDim myArray(0 To dim1ubound, 0 To dim2ubound - 1)
For i1 = 0 To dim1ubound
For i2 = 0 To dim2ubound - 1
myArray(i1, i2) = rs(i2)
Next
rs.MoveNext
Next
RsToArray = myArray
End Function

Open in new window

SOLUTION
Avatar of VTKegan
VTKegan
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Robert Berke

ASKER

Getrows looks like it will be exactly what I want (other than a superficial difference in that it is the transpose of what I wanted).  

And, I actually agree with comments about keeping the recordset intact 99% of the time.

But maybe 1% of the time using a matrix/array approach is useful.  

By analogy, an Excel Range is also essentially an array, yet about 5% of the time it is convenient for me to stick a range into a vba array.  In fact, I have found that quite a few Excel Gurus like to use the ary = Range approach for certain functions.

Thanks for your help.

rberke