To those who say, "Oh, the query dialog willMy Query Builder will produce Queries faster for me than your approach of
produce queries faster than you can script them."
I reply, "No, the query dialog will produce queries
faster than YOU can script them."
writing SQL from scratch will produce Queries for me. Obviously, YMDV. When
Access first was released, I was "like the lightning bug that backed into
the electric fan -- de-lighted, no end" because for US$88.88 I had just
"hired an assistant to do the grunt work of writing SQL for me".
I can modify the Query if I wish, and the basics are there. I almost always
wish to modify the generated Queries if I am putting the SQL into a code
module. On the other hand, when I generate a Query and it works
satisfactorily (all things considered), I don't wish to modify it. If you
store it as a Query, Access will "have its way with it" before you see it
again, anyway.
My experience parallels yours on saved queries not being the great
performance advantage they are often touted to be. In part, that's because
changes to the data in the data sources of the queries affect the
optimization; in even larger part, as you pointed out (I think you did but
I'm too lazy to go back and re-read all the posts) to the fact that
virtually all the Queries I execute limit the records to be retrieved with a
WHERE, HAVING, or JOIN and retrieving a different record, or different
records also affects the optimization. The exception is Action Queries that
I want to affect every Record.
Larry Linson
Microsoft Office Access MVP
