Solved Curious on Multidimension arrays (1 Viewer)

[mdnuts]

good morning,

I do not usually use multidimension arrays but a project i am working on has a good use for it - i just need to quickly compare data.

The table is 4 fields and about 168 records. When i use

dim reqAP as Variant
reqAP = rst.GetRows(rst.RecordCount)

it pulls it into an array, however the array is reqAP(167,3) instead of reqAP(3,167). I know i can iterate over the recordset and create the array - but is there a quicker way to go about it?

 

[Ranman256]

all tables are 2 dimensional as is your example array.
the table/recordset IS the array, what more do you want to do with it?

 

[plog]

Perhaps just a SELECT query.

Define "comparing". Please explain the ultimate aim of what you want to accomplish.

 

[arnelgp]

you already have recordset on your hand, why switch to jurassic method.

ubound(array, 1) = number of rows (+1)
ubound(array, 2) = number of columns (+1)

resulting array is 0 based,

array(col, row)

to get the first row and second column:
?array(1, 0)

 

[ebs17]

however the array is reqAP(167,3) instead of reqAP(3,167)

It is like that. The GetRows method creates a transposed array in both DAO and ADODB.

What's more unusual is "needing" an array to compare table data.

 

[MajP]

but is there a quicker way to go about it?

Most likely a simple query will be orders of magnitude faster than interation. But you have to explain what you mean by "compare data"

 

[Gasman]

good morning,

I do not usually use multidimension arrays but a project i am working on has a good use for it - i just need to quickly compare data.

The table is 4 fields and about 168 records. When i use

dim reqAP as Variant
reqAP = rst.GetRows(rst.RecordCount)

it pulls it into an array, however the array is reqAP(167,3) instead of reqAP(3,167). I know i can iterate over the recordset and create the array - but is there a quicker way to go about it?

Read this thread.

Look "ahead" in a record set - Eileen's Lounge

Or just this link, but read throroughly. The other poster did not.

Recordset.GetRows method (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

 

[mdnuts]

Most likely a simple query will be orders of magnitude faster than interation. But you have to explain what you mean by "compare data"

take one value and see if it matches another.

 

[The_Doc_Man]

take one value and see if it matches another.

Your problem is still not clear. That could end up being a Cartesian (a.k.a. permutation) class of operation or it could a simple JOIN operation. We need a LOT more details than you have provided.

Are you looking for specific rows to match other specific rows or are you trying to see if ANY arbitrary row in one array matches ANY arbitrary row in another array. Are you looking for a specific field in one recordset to match a specific field in the other recordset, or is it more like matching any field in the record against any field in the other record on which you are comparing?

If it is the "any field & record / any other field & record case" you have the worst of all possible searches, as if you have M rows in one recordset and N rows in the other and P fields in one and Q fields in the other, the general any/any match will ALWAYS required M*N*P*Q comparisons no matter what you do.

HOWEVER, there are other ways to search. Without knowing the real goal, it would be hard to direct you.

 

[mdnuts]

Read this thread.

Look "ahead" in a record set - Eileen's Lounge

Or just this link, but read throroughly. The other poster did not.

Recordset.GetRows method (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

in fact it's right at the very top of that link. thank you.

 

[MajP]

take one value and see if it matches another.

Then why are you wasting time with arrays? That is a simple query.