I need a scredriver, and all I have are hammers...

[sgbotsford]

I'm starting to have a real appreciation for access, but I'll admit
there are some frustrations. I'm trying to figure out how to repeat a
similar action without having to create and save dozens of similar
queries.

I have a small database with a thousand locations in it. Each location
has GPS coordinates. Each location is assigned a zone.

For use with the GPS, I want to write out each zone as a text file in
suitable format for my mapping program. At this point the only way
I've been able to figure out how to do this is to write a bunch of
querries.

Querry_A
Create a querry, where Zone is like "A"

Querry_B
Create a querry where zone is like "B"

....

Then create a macro that uses TransferText for each query. This is
tedious.

How do I create a loop construct? In psuedo code what I want to do is
this:

foreach zone (A..Z) {
Select from Table GPS where GPS!Zone = $Zone output to table temp
TransferText temp , comma_delimited, $Zone.txt
TransferText temp, excel, $Zone.xls
}

This is a siimplification. I want different fields for the excel
spreadsheet than for the text file, but this illustrates what I'm
trying to do.

I think this can all be done in visual basic (A language I don't speak
yet...) and hence I should be able to do this entirely within access,
but I can''t figure out the hooks.

I need pointers to how to perform iteration in access.

 

[Chris2]

I'm starting to have a real appreciation for access, but I'll admit
there are some frustrations. I'm trying to figure out how to repeat a
similar action without having to create and save dozens of similar
queries.

I have a small database with a thousand locations in it. Each location
has GPS coordinates. Each location is assigned a zone.

For use with the GPS, I want to write out each zone as a text file in
suitable format for my mapping program. At this point the only way
I've been able to figure out how to do this is to write a bunch of
querries.

Querry_A
Create a querry, where Zone is like "A"

Querry_B
Create a querry where zone is like "B"

...

Then create a macro that uses TransferText for each query. This is
tedious.

How do I create a loop construct? In psuedo code what I want to do is
this:

foreach zone (A..Z) {
Select from Table GPS where GPS!Zone = $Zone output to table temp
TransferText temp , comma_delimited, $Zone.txt
TransferText temp, excel, $Zone.xls
}

sgbotsford,

Looping:

Do Until <condition>
<code to repeat>
Loop

Exam:

With rs
.MoveFirst
Do Until .Eof
strFileName = .Fields("Zone") & _
".xls"
<TransferText Code using strFileName as a parameter, etc.>
.MoveNext
Loop

End With

Also:

Do While <condition>
<code to repeat>
Loop

For <variable> = <literal | variable> to <literal | variable>
<code to repeat>
Next

That's the basics. There is also While/Wend, and putting the
Until|While of Do after Loop instead of after Do.

ForEach is for iterating over object collections. You could do this
if you wanted to, but it would involve creating a class,
instantiating an object, loading the object with the zones or
locations as mentioned above, and then iterating over that, etc.

For your purposes, you'll use string assembly to make new output
file names as each iteration of the loop repeats (in a manner
similar to what the example above shows).


Sincerely,

Chris O.

 

[jacksonmacd]

Are all the queries identical except for the zone? If so, you should
create one query, using the zone as a parameter. That way, when you
open the query, Access will ask you for the Zone identifier, then
proceed with executing the query for that zone. You would only need to
maintain one query, which is a simplification compared to maintaining
a separate query for each zone.

Presuming that you are dealing with only one zone at a time, you could
execute your macro that would open the query and ask for the zone. The
macro would execute the query and proceed with the subsequent steps.

I'm starting to have a real appreciation for access, but I'll admit
there are some frustrations. I'm trying to figure out how to repeat a
similar action without having to create and save dozens of similar
queries.

I have a small database with a thousand locations in it. Each location
has GPS coordinates. Each location is assigned a zone.

For use with the GPS, I want to write out each zone as a text file in
suitable format for my mapping program. At this point the only way
I've been able to figure out how to do this is to write a bunch of
querries.

Querry_A
Create a querry, where Zone is like "A"

Querry_B
Create a querry where zone is like "B"

...

Then create a macro that uses TransferText for each query. This is
tedious.

How do I create a loop construct? In psuedo code what I want to do is
this:

foreach zone (A..Z) {
Select from Table GPS where GPS!Zone = $Zone output to table temp
TransferText temp , comma_delimited, $Zone.txt
TransferText temp, excel, $Zone.xls
}

This is a siimplification. I want different fields for the excel
spreadsheet than for the text file, but this illustrates what I'm
trying to do.

I think this can all be done in visual basic (A language I don't speak
yet...) and hence I should be able to do this entirely within access,
but I can''t figure out the hooks.

I need pointers to how to perform iteration in access.

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security