Robert Berke
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?
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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