How stop access query from replacing our SQL code

  • Thread starter Thread starter Suzi Carr
  • Start date Start date
S

Suzi Carr

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
 
Suzi Carr said:
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
Click to expand...

Don't open the queries in the query designer, or, if you do, don't save
them.
 
Back
Top