?
Solved

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

Posted on 2011-02-17
3
Medium Priority
?
968 Views
Last Modified: 2012-06-22
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

0
Comment
Question by:rberke
3 Comments
 
LVL 10

Assisted Solution

by:VTKegan
VTKegan earned 80 total points
ID: 34916462
A recordset is an array essentially.  You access rows and columns using indexes directly on the recordset.  To me it doesn't make sense doing what you are doing.

I would just operate on the recordset instead of creating my own matrix of a recordset.
0
 
LVL 28

Accepted Solution

by:
omgang earned 920 total points
ID: 34916498
I agree with VTKegan and prefer to work with the recordset directly.  But to your question the GetRows method may be worth looking into.  It will populate an array with records from a recordset.  It does the same thing as your function but will do so with a few less lines of code.
http://msdn.microsoft.com/en-us/library/bb221041(v=office.12).aspx

OM Gang
0
 
LVL 5

Author Comment

by:rberke
ID: 34917274
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

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question