Getrows (1 Viewer)

kirkm

Registered User.
Local time
, 03:39
Joined
Oct 30, 2008
Messages
956
It doesn't matter why I want one, does it ? Or what I do with it ? The question is about creating it.
 

June7

AWF VIP
Local time
Today, 06:39
Joined
Mar 9, 2014
Messages
3,325
GetRows works for me with a single field ADO or DAO recordset.
Code:
Sub test1()
Dim rs As ADODB.Recordset, sAry As Variant, r As Integer, c As Integer
Set rs = New ADODB.Recordset
rs.Open "SELECT Rate FROM Rates", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
sAry = rs.GetRows
Debug.Print UBound(sAry, 1)
Debug.Print UBound(sAry, 2)
For r = 0 To UBound(sAry, 1)
    For c = 0 To UBound(sAry, 2)
        Debug.Print sAry(r, c)
    Next
Next
End Sub

Sub test2()
Dim rs As DAO.Recordset, sAry As Variant, r As Integer, c As Integer
Set rs = CurrentDb.OpenRecordset("SELECT Rate FROM Rates")
rs.MoveLast
rs.MoveFirst
sAry = rs.GetRows(rs.RecordCount)
Debug.Print UBound(sAry, 1)
Debug.Print UBound(sAry, 2)
For r = 0 To UBound(sAry, 1)
    For c = 0 To UBound(sAry, 2)
        Debug.Print sAry(r, c)
    Next
Next
End Sub
Only thing I can think of to do with an array is to loop through elements and do something with each. So if a loop is needed, why not just loop recordset? I am not saying shouldn't use an array - I have constructed arrays from recordsets because it facilitated particular data manipulation - just wondering if it really is necessary for your situation.

Now I will leave you to it and wish you luck with your project.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 07:39
Joined
Oct 29, 2018
Messages
12,078
My goal wss to create an array from a (single field) Recordset, using some preexisting command, as opposed to looping through each entry. Like GetRows, but it's not good enough. Getstring is also no good (no delimiter to split on). So the answer is you move through the recordset building your array. Or you use Getrows and move through that cleaning it up!
Maybe someone can explain why MS thought making getrows 2 dim was a good thing. Isn't one of the dimensions immediately obsolete? Am I missing something?
Hi. GetString worked for me after I provided the RowDelimiter argument.
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 09:39
Joined
Feb 28, 2001
Messages
17,781
I'm confused over your expectations here. GetRows will ALWAYS return two dimensions because a recordset is always potentially two dimensional. The mere fact that you only have one field doesn't change anything. The dimensions are the field number and record number, but both are zero-based as is typical within Access collections. GetRows has to be coded to return the general case. They can't be expected to tailor the method to special cases. Just remember, every exceptional case is another bug lurking in the shadows waiting to pounce.

If you look at the way that you access the fields in a combo-box (think .Columns property), you have to include the row index and the column index there, too, even if it is a one-column box. For accessing a 2D array, even if using the default, you still have to have a place-holder for it since the index to the row is the 2nd argument.

kirkm said:
Maybe someone can explain why MS thought making getrows 2 dim was a good thing.
I'm putting words in someone else's mouth here, perhaps, but I'll bet MS didn't just think that making GetRows was a good thing; it was a NECESSARY thing. You see, you HAPPEN to have a one-field recordset. But GetRows is for people who have N-field recordsets, where N can be 1 - but doesn't have to be only 1.

Remember that for typical Access collections, everything is zero-based, so that first index will always be zero (i.e. first column) for YOUR CASE. But what you are really doing for any given row (seelcted by the 2nd array dimension) is asking for RS.Fields(n) where n is the field index and is also the 1st array dimension.
 

kirkm

Registered User.
Local time
, 03:39
Joined
Oct 30, 2008
Messages
956
Much appreciated the example/description from DocMan and June. Kind of a Eureka moment. It does make a lot more sense whem more than one field is involved and the result from GetRows is Arr(ColNumber, Row Number). Guess I got hung up on ColNumber being included when it was 0.
I did a bit of testing with ADO and DAO and getRows and getString. As DBGuy said ADO/GetString + Split gives the desired result. The delimiter wasn't first apparent from looking at the result in the Watches window. I found it defaults to vbcr (where some say it's vbcrlf)

I'll change my code to handle the 2 dim array format (which is what you all suggested) rather than getString as DAO is a bit simpler (no Cursor Type/ Connection needed).

Thanks for all the replies. Been very helpful.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom