Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-07-2019, 09:55 PM   #16
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,625
Thanks: 1
Thanked 630 Times in 623 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Getrows

Why use array and not loop through recordset? What are you really trying to accomplish?

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
June7 is offline   Reply With Quote
Old 12-07-2019, 10:08 PM   #17
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 760
Thanks: 28
Thanked 24 Times in 24 Posts
kirkm is on a distinguished road
Re: Getrows

> 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).
kirkm is offline   Reply With Quote
Old 12-07-2019, 10:10 PM   #18
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,625
Thanks: 1
Thanked 630 Times in 623 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Getrows

So what do you do with the array? I still don't know what you are really trying to accomplish.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
June7 is offline   Reply With Quote
Old 12-07-2019, 11:25 PM   #19
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 760
Thanks: 28
Thanked 24 Times in 24 Posts
kirkm is on a distinguished road
Re: Getrows

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?
kirkm is offline   Reply With Quote
Old 12-08-2019, 12:06 AM   #20
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,625
Thanks: 1
Thanked 630 Times in 623 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Getrows

But WHY do you want an array. What will you do with it once it is built?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
June7 is offline   Reply With Quote
Old 12-08-2019, 01:11 AM   #21
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 760
Thanks: 28
Thanked 24 Times in 24 Posts
kirkm is on a distinguished road
Re: Getrows

It doesn't matter why I want one, does it ? Or what I do with it ? The question is about creating it.
kirkm is offline   Reply With Quote
Old 12-08-2019, 01:20 AM   #22
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,625
Thanks: 1
Thanked 630 Times in 623 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Getrows

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.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Last edited by June7; 12-08-2019 at 12:11 PM.
June7 is offline   Reply With Quote
Old 12-08-2019, 07:00 AM   #23
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,291
Thanks: 66
Thanked 1,684 Times in 1,639 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Getrows

Quote:
Originally Posted by kirkm View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 12-08-2019, 07:30 AM   #24
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 15,102
Thanks: 102
Thanked 1,897 Times in 1,731 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Getrows

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.

Quote:
Originally Posted by kirkm
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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
Old 12-08-2019, 01:40 PM   #25
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 760
Thanks: 28
Thanked 24 Times in 24 Posts
kirkm is on a distinguished road
Re: Getrows

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.

kirkm is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Why I am not able to get all records by excluding the Numrows argument in getrows()? prabha_friend Modules & VBA 10 08-01-2014 02:02 AM
Getrows method not working prabha_friend Modules & VBA 25 07-31-2014 06:28 AM
getrows not retriving all records bobmac- Modules & VBA 2 01-17-2011 01:52 PM
GetRows. Where is PutRows Soton Modules & VBA 1 03-15-2010 11:45 AM
Populating Variant Array With GetRows cameron.scrimgeour Modules & VBA 2 05-07-2009 02:55 AM




All times are GMT -8. The time now is 12:21 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World