Advice on looping through a set of arrays please 
Author Message
 Advice on looping through a set of arrays please

Hi all,

Please bear with me on this one as it's kinda complicated - well for
me it is anyway. Just ask if you need anymore information and I'll see
what I can offer.

Okay I'm writing some VBA to loop through a set names (for example
purposes lets say 10) for which there are a number of entries (5 for
the example) over a given number of years (again for example lets say
three).

Now the best way I thought of doing this was having 3 arrays, with 5
positions. So Year1(5), Year2(5) and Year3(5). Then looping through
each of the names and populating the arrays, so:

For i = 1 to 10

set sql
open recordset
Year1(1 to 5) = whatever is in the database

set sql
open recordset
Year1(1 to 5) = whatever is in the database

set sql
open recordset
Year1(1 to 5) = whatever is in the database

next i

Now what I've found is that I've duplicated quite a bit of code for
each of the year arrays. In fact the only difference is Year1, Year2
and Year3. So what I'd like to try and do is reference each of the
arrays dynamically. Something like:

For i = 1 to 10
For j = 1 to 3
set sql
open recordset
Yearj(1 to 5) = whatever is in the database
next j
next i

And here is where I am running into problems as whatever I try just
simply isn't recognised within vba.

Can anyone offer some advice? Am I going about this all wrong?

TIA

Brad



Sat, 12 Mar 2005 03:59:06 GMT  
 Advice on looping through a set of arrays please
What should be the result of this operation? It looks to me as a lot of work
for something that might nicely fit into a query, or maybe a report.

--
Regards,
Bas Cost Budde, Nieuwegein, Holland
website at http://www.heuveltop.nl/BasCB/msac_index.html
Please post replies to the newsgroup so everyone can benefit

Quote:

>Now the best way I thought of doing this was having 3 arrays, with 5
>positions. So Year1(5), Year2(5) and Year3(5). Then looping through
>each of the names and populating the arrays, so:



Sat, 12 Mar 2005 04:13:02 GMT  
 Advice on looping through a set of arrays please
probably .GetRows method can help you

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com


Quote:
> Hi all,

> Please bear with me on this one as it's kinda complicated - well for
> me it is anyway. Just ask if you need anymore information and I'll see
> what I can offer.

> Okay I'm writing some vba to loop through a set names (for example
> purposes lets say 10) for which there are a number of entries (5 for
> the example) over a given number of years (again for example lets say
> three).

> Now the best way I thought of doing this was having 3 arrays, with 5
> positions. So Year1(5), Year2(5) and Year3(5). Then looping through
> each of the names and populating the arrays, so:

> For i = 1 to 10

> set sql
> open recordset
> Year1(1 to 5) = whatever is in the database

> set sql
> open recordset
> Year1(1 to 5) = whatever is in the database

> set sql
> open recordset
> Year1(1 to 5) = whatever is in the database

> next i

> Now what I've found is that I've duplicated quite a bit of code for
> each of the year arrays. In fact the only difference is Year1, Year2
> and Year3. So what I'd like to try and do is reference each of the
> arrays dynamically. Something like:

> For i = 1 to 10
> For j = 1 to 3
> set sql
> open recordset
> Yearj(1 to 5) = whatever is in the database
> next j
> next i

> And here is where I am running into problems as whatever I try just
> simply isn't recognised within vba.

> Can anyone offer some advice? Am I going about this all wrong?

> TIA

> Brad



Sat, 12 Mar 2005 15:49:15 GMT  
 Advice on looping through a set of arrays please
The ultimate aim is for around 850 entries to be outputted to a csv
file with around 20 data items over 5 years. The setup is that I'm
using access to connect to a SQL Server, and I can't use a normal
access query/report as certain tables within the SQL database have
over 255 fields. Hence writing some code to extract all the details
and then output to a file.

Does that sound like the right way of doing this or have I missed
something with the queries?


Quote:
> What should be the result of this operation? It looks to me as a lot of work
> for something that might nicely fit into a query, or maybe a report.

> --
> Regards,
> Bas Cost Budde, Nieuwegein, Holland
> website at http://www.heuveltop.nl/BasCB/msac_index.html
> Please post replies to the newsgroup so everyone can benefit



Sat, 12 Mar 2005 17:44:28 GMT  
 Advice on looping through a set of arrays please
Well, I never! An SQL database table with over 255 fields? Amazing. Does
that break a query? I know that an Access table (I mean, in a Jet database)
cannot contain more than 255 fields, but for a query I haven't heard of such
a limitation.

If you are taking values from a table, and putting them into a file, you
don't need arrays first. Unless you planned to do calculations. Poor man.
Maybe the database can produce useful queries for you? I mean, server side
queries. You might be able to link these as tables in Access, which is quite
a lot easier to export into a file.

Please do understand that I am not trying to circumvent your question - if
code is needed, that can be worked out. It would be a Complete Waste of
Time, however (thanks John Cleese) if this could be done in a query. You are
talking about "opening a recordset" which would amount to using some sort of
query. You'd HAVE to, otherwise you cannot reach your data at all.
--
Regards,
Bas Cost Budde, Nieuwegein, Holland
website at http://www.heuveltop.nl/BasCB/msac_index.html
Please post replies to the newsgroup so everyone can benefit

Quote:

>The ultimate aim is for around 850 entries to be outputted to a csv
>file with around 20 data items over 5 years. The setup is that I'm
>using access to connect to a SQL Server, and I can't use a normal
>access query/report as certain tables within the SQL database have
>over 255 fields. Hence writing some code to extract all the details
>and then output to a file.

>Does that sound like the right way of doing this or have I missed
>something with the queries?




Quote:
>> What should be the result of this operation? It looks to me as a lot of
work
>> for something that might nicely fit into a query, or maybe a report.

>> --
>> Regards,
>> Bas Cost Budde, Nieuwegein, Holland
>> website at http://www.heuveltop.nl/BasCB/msac_index.html
>> Please post replies to the newsgroup so everyone can benefit



Sat, 12 Mar 2005 17:58:09 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Control Array's Advice Required Please

2. Seeking advice about looping code in VB.

3. Array has x elements. Set array(x) = newval

4. structure array advice

5. Advice on saving array of users preferences

6. Help -- Loop, Array, Collection??

7. UnLock an array in a for loop.

8. Using Array while looping through database

9. Array Looping

10. Dynamic Array and looping (newbie)

11. Need help with Loop/Control Array.

12. arrays/nested loops-simple question

 

 
Powered by phpBB® Forum Software