Getrows (1 Viewer)

kirkm

Registered User.
Local time
Today, 22:17
Joined
Oct 30, 2008
Messages
934
Seems .GetRows returns a 2 dimensional array.
If you just want a single dimension is there a switch or method for this, as iterating to create a 1 dimensional array from 2 you may as well do this with the recordset and not bother with .Getrows ?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:17
Joined
Oct 29, 2018
Messages
11,837
Hi. I think it is two-dimensional if you are retrieving multiple fields. But if you only retrieve one field, maybe it will be a one-dimensional array. Have you tried that? Just a thought...
 

kirkm

Registered User.
Local time
Today, 22:17
Joined
Oct 30, 2008
Messages
934
The argument for .Getrows is .Recordcount (if I'm right!). My recordset holds just a single field but .Getrows returns -


- MyGetRows(0)
MyGetRows(0,0) "\Disk1"
MyGetRows(0,1) "\Disk2"
MyGetRows(0,2) "\Disk3"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:17
Joined
Oct 29, 2018
Messages
11,837
The argument for .Getrows is .Recordcount (if I'm right!). My recordset holds just a single field but .Getrows returns -


- MyGetRows(0)
MyGetRows(0,0) "\Disk1"
MyGetRows(0,1) "\Disk2"
MyGetRows(0,2) "\Disk3"
I see. I guess not then. However, if you're looking for another method, then maybe you can use GetString and then use Split() to put the data in a one-dimensional array. Just a thought...
 

vba_php

Forum Troll
Local time
Today, 05:17
Joined
Oct 6, 2019
Messages
2,884
what's wrong with using GetRows() anyway? It doesn't matter if the 2nd dimension is always empty. You can just throw the data from dim. 1 to where ever else it is you're taking it, can't you? The purpose of that function IS to return a 2 dimensional.
 

kirkm

Registered User.
Local time
Today, 22:17
Joined
Oct 30, 2008
Messages
934
Adam, how would you address a 2 dim array when a 1 dim is expected?
I've never heard of GetString. Found this example if rs.recordcount <> 0 the vara = rs.getstring but it doesn't work here. Is a reference needed?
 

vba_php

Forum Troll
Local time
Today, 05:17
Joined
Oct 6, 2019
Messages
2,884
Adam, how would you address a 2 dim array when a 1 dim is expected?
well according to your previous post:
My recordset holds just a single field but .Getrows returns -

- MyGetRows(0)
MyGetRows(0,0) "\Disk1"
MyGetRows(0,1) "\Disk2"
MyGetRows(0,2) "\Disk3"
when you use getrows(), a 1 dim is NOT expected. To correct my first post, the 2nd dimension is *not* empty because it refers to the row that you're pointing to. so why cant you just loop through the array and do whatever you want with the data? you don't have to hard code it line by line like in your above quote. I have a FAQ thread on this forum somewhere under the name "ajetrumpet" that is titled "Sort Array" or something similar. It shows you how to loop a 1 dim and 2 dim. That might help too. Have a look at it. furthermore, run the code "get_rows()" in the attached file and see what I mean. where it echoes the values out, do what you want with it in your own routine.
 

Attachments

Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 03:17
Joined
Oct 29, 2018
Messages
11,837
I've never heard of GetString. Found this example if rs.recordcount <> 0 the vara = rs.getstring but it doesn't work here. Is a reference needed?
Hi. What does "doesn't work" mean? Did you get an error message? What did it say? What data type is vara?
 

June7

AWF VIP
Local time
Today, 02:17
Joined
Mar 9, 2014
Messages
3,292
GetString is a method of ADO recordset.
 

vba_php

Forum Troll
Local time
Today, 05:17
Joined
Oct 6, 2019
Messages
2,884
I think ADO is less commonly used than DAO by access people. I think ADO is a bit more complicated.
 

June7

AWF VIP
Local time
Today, 02:17
Joined
Mar 9, 2014
Messages
3,292
OP never specified but since they said "it didn't work" I am guessing they are using DAO because GetString won't show with intellisense for DAO recordset. GetRows shows for both. But they really need to clarify "it didn't work".

Yes, setting and opening is a little more complicated. This worked:
Code:
Sub Test()
Dim rs As ADODB.Recordset, sAry As Variant, x As Integer
Set rs = New ADODB.Recordset
rs.Open "SELECT Rate FROM Rates", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
sAry = Split(rs.GetString)
For x = 0 To UBound(sAry)
    Debug.Print sAry(x)
Next
End Sub
ADO used to be the standard back with - I think - Access 2003 (was required for ADP).
Also think ADO is necessary for SQLServer connections and Excel automation.
Some have opinion ADO is simpler.
 
Last edited:

kirkm

Registered User.
Local time
Today, 22:17
Joined
Oct 30, 2008
Messages
934
Thanks June, that explains it. With DAO the error was "Method or Data member Not Found"
"vara" wasn't defined (it was a paste ffrom a Google search)
> so why cant you just loop through the array and do whatever you want with the data?

Because it's in a For-Next Loop where the Array may be 1 dim from a different condition.
It would mean changing one or the other. Very possible of course, but if getrows worked as hoped neither change is needed. (msg 3 paste is from a Watch). Just about to look at your sample..it may help. But why 2 dim when only 1 field exists I don't get. What use is it?
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 05:17
Joined
Feb 28, 2001
Messages
17,705
vba_php said:
I think ADO is less commonly used than DAO by access people. I think ADO is a bit more complicated.
Don't forget, Adam, that when MS was trying to find a way to do Access-based web pages, they tried to emphasize ADO for web situations. However, that attempt didn't go over so well (can you say "lead balloon"?) and has officially been abandoned. At the time of that abandonment, they made DAO the preferred method again. But for some versions of Access, ADO is the default for others, DAO is the default.

Therefore, which one you use might depend on which version of Access was in force when you originally developed the app.
 

kirkm

Registered User.
Local time
Today, 22:17
Joined
Oct 30, 2008
Messages
934
Adam,

Debug.Print arr(0, x)

I can't use Arr(0,x) as is if Arr is 1 dim (as it may be). This was the issue.
I'll change to ADO and try GetString
 

June7

AWF VIP
Local time
Today, 02:17
Joined
Mar 9, 2014
Messages
3,292
Why use array and not loop through recordset? What are you really trying to accomplish?
 

kirkm

Registered User.
Local time
Today, 22:17
Joined
Oct 30, 2008
Messages
934
> Why use array and not loop through recordset?
I used getRows to avoid looping through anything and convert the recordset to an array, except a 2 dim array isn't going to work. (with existing routine that expects 1 dim array).
 

June7

AWF VIP
Local time
Today, 02:17
Joined
Mar 9, 2014
Messages
3,292
So what do you do with the array? I still don't know what you are really trying to accomplish.
 

kirkm

Registered User.
Local time
Today, 22:17
Joined
Oct 30, 2008
Messages
934
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?
 

June7

AWF VIP
Local time
Today, 02:17
Joined
Mar 9, 2014
Messages
3,292
But WHY do you want an array. What will you do with it once it is built?
 

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

Top Bottom