run queries by the batch

[Tonk]

Sorry to pollute the post with so many dumb questions! Until I figure
out how to write queries that accomplish more than 1 thing at a time, I
was wondering if there was any way to automate queries. For example, I
have a delete query that deletes bad data. I run an update query that
changes sex from 2 to 1 if conditions x, y, and z are met. I run
another delete query that checks for missing values. And so on, and so
forth.

I guess what I'm looking for is a way to run a batch file that will run
a series of queries one right after the other.

Am I nuts or does such capability exist?

tonk

 

[Rick Brandt]

Sorry to pollute the post with so many dumb questions! Until I figure
out how to write queries that accomplish more than 1 thing at a time,
I was wondering if there was any way to automate queries. For
example, I have a delete query that deletes bad data. I run an
update query that changes sex from 2 to 1 if conditions x, y, and z
are met. I run another delete query that checks for missing values.
And so on, and so forth.

I guess what I'm looking for is a way to run a batch file that will
run a series of queries one right after the other.

Am I nuts or does such capability exist?

tonk

In VBA code you can have...

CurrentDB.Execute "FirstQueryName", dbFailOnError
CurrentDB.Execute "SecondQueryName", dbFailOnError
CurrentDB.Execute "ThirdQueryName", dbFailOnError
etc..

 

[Tonk]

Rick - Thank you. Is that a common practice? I'll experiment with
that. Is that a common practice and if so, under what conditions would
one normally want to use such code?

Mike

 

[Rick Brandt]

Rick - Thank you. Is that a common practice? I'll experiment with
that. Is that a common practice and if so, under what conditions
would one normally want to use such code?

I don't think it is particularly common to need to run multiple action queries
in sequence, but when that is required then executing them in a VBA procedure as
in my example is the most straightforward way to do it that I can think of.

If you are doing this to clean up bad data then perhaps you should explore why
you are getting bad data in the first place and prevent it at the source.

 

[Tonk]

Rick - Thanks for the reply. As for the bad data, it comes with the
territory. I'm dealing with nearly 250,000 forms complete by nearly
that many people, some who don't care if I can read the form and others
who just can't read . Some clean up is a reality. Ultimately, I
will be writing sophisticated queries, but until that time... Which
brings me to a question I plan to post to the group - I've got two
books - Michael Alexander's - Microsoft Access Data Analysis and
Virginia Andersens' MS Access 2002 - The Complete Reference. Both are
good books, espeically Alexander's, but only scratch the surface on
subqueries and the like. Could you recommend a book that covers
queries in great depth?

Thanks Rick and Happy New Year!

Mike