If you save a querydef (or use a named querydef), Access may
alter the SQL. To avoid this, don't save querydefs, and don't
use named querydefs.
Sometimes you can avoid using named querydefs by using
..Execute, and storing the sql in some table other than the
system querydef table. Of course, that won't help you if
you are using nested query references.
If you are using nested query references, sometimes it helps
if you are just more careful about the sql you write. For
example, I see that in the example you give, your original
SQL could be replaced by one line from the Access generated
SQL:
OR ( ((Yr)=2004 And (Yr)=2004) AND ((Mo)>=4 And (Mo)<=6) )
which can be replaced by
(((Yr)=2004) AND ((Mo)>=4 And (Mo)<=6))
which will not be re-written by Access at all.
More generally, the example you gave is slow because the
generated SQL is evaluated in order, and always evaluates a
number of false expressions before completing the test. Keeping
the general form (to stop Access re-writing your SQL) you could
speed it up by changing the order of the expressions:
WHERE (((Yr)=2004) AND ((Mo)>=4 And (Mo)<=6)) or (((Yr)>2004 And (Yr)<2004))
OR (((Yr)=2004 And (Yr)<2004) AND ((Mo)>=4))
OR (((Yr)>2004 And (Yr)=2004) AND ((Mo)<=6))
The first term of the expression will be true, so none of
the other terms will be evaluated.
(david)
"Suzi Carr" <sc******@yahoo.com> wrote in message
news:HQVgd.192873$a85.120019@fed1read04... Hello,
We create querydefs in VB programs (i.e. CreateQueryDef). But as
illustrated below, Access regenerates the SQL code we specify -- particularly the
WHERE clause. While the new code is logically equivalent, it often results in
MUCH slower query performance and this has become a big problem in some
applications. If we go to query design mode, we can see that our code was
replaced. When we manually change it back, the query runs much faster, but
we need to generate these queries on the fly.
In VB code, we created a querydef with the following SQL code:
SELECT * FROM Table1
WHERE ([Yr]>2004 OR ([Yr]=2004 AND [Mo]>=4)) AND ([Yr]<2004 OR ([Yr]=2004
AND [Mo]<=6))"
but in "SQL View" we see it was replaced with the following:
SELECT * FROM Table1
WHERE (((Yr)>2004 And (Yr)<2004))
OR (((Yr)=2004 And (Yr)<2004) AND ((Mo)>=4))
OR (((Yr)>2004 And (Yr)=2004) AND ((Mo)<=6))
OR (((Yr)=2004 And (Yr)=2004) AND ((Mo)>=4 And (Mo)<=6))
Does anyone know how to prevent Access from overriding our SQL code?
Thanks,
Suzi