473,326 Members | 2,061 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

How stop access query from replacing SQL code with its own

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
Nov 13 '05 #1
1 1951
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

Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Vladimir | last post by:
Hello, I have a table in MS Access database. It has one field (with BYTE datatype) that has several properties set in Lookup tab of table Design View. Display Control = Combo Box. Row Source...
2
by: Daniel | last post by:
I use an Access database to basically take data exports, import them, manipulate the data, and then turn them into exportable reports. I do this using numerous macros, and queries to get the data...
1
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a...
4
by: Sami | last post by:
I hope someone will tell me how to do this without having to do any VB as I know nothing in that area. I am a rank beginner in using Access. I have created a database consisting of student...
15
by: brettclare | last post by:
I have linked a large SQL Server table to Access, however 'only' 2,195,439 records are shown and are available to query. Can I increase the size (cache??)/number of records showing in Access? ...
7
by: rguarnieri | last post by:
Hi! I'm trying to create a query with a boolean expression like this: select (4 and 1) as Value from Table1 this query return always -1, but when I make the same calculation in visual...
13
by: G | last post by:
Hello, Looking for opinions on a fairly simple task, new to ASP.net (C#) and want to make sure I do this as efficiently as possible. I have a web based form, and I need to run some SQL before...
5
by: Simon | last post by:
Dear reader, With the export command you can export a query to Excel. By activate this command a form pop's up with the following text:
10
by: Lindsay Browning | last post by:
Hello, I found a previous posting by someone on the 10th Sept (http://www.thescripts.com/forum/thread705528.html) who seemed to have the same problem as me, but it was never resolved. I have...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.