How do I load data from a select query into an Array in VBA code?

  • Thread starter Thread starter BlueWolvering
  • Start date Start date
B

BlueWolvering

I want to load the data in a select query into array(s) in VBA code so that I
can manipulate the data. (ACCESS 2003)

Query has 7 fields (call them A, B, C, D, E, F, G) for brevity.
There could be any number of records in this query.

I want to load the data into array(s). I was thinking that the index on the
1 dimensional arrays would serve to indicate which record I was looking at.

so arrayA(1) = value in first record, first field.
arrayB(1) = value in first record, second field.
arrayB(2) = value in second record, second field.

I need to have data out of the query to perform many actions, so loading the
data into VBA is NON negotiable, but the data storage method is.

I want something that should work like this.

i=1
For each Record in SelectQuery

A(i) = record.A
B(i) = record.B
..
..
..
G(i) = record.G
i=i+1
Next Record
 
BlueWolvering said:
I want to load the data in a select query into array(s) in VBA code so that
I
can manipulate the data. (ACCESS 2003)
Click to expand...

Why not just open the recordset and manipulate the fields in it?

Tom Lake
 
I don't know how?
Can you provide pseudocode for that in the same sense that I gave?
(Your answer is probably exactly what I am searching for.)
 
BlueWolvering said:
I want to load the data in a select query into array(s) in VBA code so that I
can manipulate the data. (ACCESS 2003)
Click to expand...

See the Sample Code illustrating looping through a DAO recordset page
at the Access Email FAQ at
http://www.granite.ab.ca/access/email/recordsetloop.htm

To dimension arrays you will need to add the following to the
recordset logic just after opening the recordset

Dim A() As String

rs.movelast
RecordCount = rs.recordcount

ReDim A(RecordCount)

Or if you know the number of fields in the recordset you could use an
array of variants and make it a two dimensional array.

Dim Array(9,1)

Redim Array(9,RecordCount)
I need to have data out of the query to perform many actions, so loading the
data into VBA is NON negotiable,
Click to expand...

Please explain more why this is non negotiable.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Ok, I suppose it's not NON Negotiable. My skill set is still a little
limited with VBA for access, though I'm pretty good with VBA for Excel. I
feel comfortable with VBA, but less so with the ACCESS specific stuff.

I am going to proceed with the method you suggest.

Here's what I need to do with data and you can tell me if there's a better
way.

I need to pull date's from the select query (3 fields) and compare them
against each other and today's date. After that I have to extract the other
fields and insert them into a string that will be the text for an sql update
query. All this is done in VBA. Are arrays a good way to store this or is
there a better way?

Thanks I appreciate the help.
 
BlueWolvering said:
Ok, I suppose it's not NON Negotiable. My skill set is still a little
limited with VBA for access, though I'm pretty good with VBA for Excel. I
feel comfortable with VBA, but less so with the ACCESS specific stuff.
Click to expand...

The differences between VBA for Excel and Access are negligible except
for the Excel/Access specific stuff. Which obviously can be
significant. But easily doable.
I am going to proceed with the method you suggest.

Here's what I need to do with data and you can tell me if there's a better
way.

I need to pull date's from the select query (3 fields) and compare them
against each other and today's date.
Click to expand...

There might be ways of doing this in queries so that you can have just
one Update query doing the whole thing. Depending on the complexity
of the logic, etc, etc.
After that I have to extract the other
fields and insert them into a string that will be the text for an sql update
query. All this is done in VBA. Are arrays a good way to store this or is
there a better way?
Click to expand...

For each record you are reading in are you then making a decision and
then updating a record? Nothing depends on data from multiple
incoming records?

If that's the case then just use variables inside the recordset
looping logic previously mentioned and update each record in the loop.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Back
Top