Preventing Access From Rewriting My Query

#1

Is there a way to preventing Access from automatically rewriting a
query? I wrote an update query to update five fields if any of four
fields in a record have a null. It runs fine but as soon as I save,
quit out of query and reopen it, the query has been rewritten to
something incredibly complex with about 200 rows that I don't even
know how to delete. It still runs correctly but Is there a way to
keep the query looking like how I wrote it? Perhaps Access sometimes
rewrites queries for optimization but it doesn't run any faster than
2-3 seconds but now it's impossible to update and maintain. Thanks
for any insight into this.

Sheldon Potolsky

#2

Sheldon wrote:

Quote:

Is there a way to preventing Access from automatically rewriting a
query? I wrote an update query to update five fields if any of four
fields in a record have a null. It runs fine but as soon as I save,
quit out of query and reopen it, the query has been rewritten to
something incredibly complex with about 200 rows that I don't even
know how to delete. It still runs correctly but Is there a way to
keep the query looking like how I wrote it? Perhaps Access sometimes
rewrites queries for optimization but it doesn't run any faster than
2-3 seconds but now it's impossible to update and maintain. Thanks
for any insight into this.

Sheldon Potolsky

Are you entering the SQL window instead of the design window. For
example, I can design a query and run it or view the SQL or see it in
design mode.

Have you compacted the database?

#3

This occurs whether I'm in the design or SQL window. I compact the
database regularly but that has no effect on the unwanted behavior I'm
seeing.

Sheldon

On Jan 22, 2:28*pm, Salad <o... (AT) vinegar (DOT) com> wrote:

Quote:

Are you entering the SQL window instead of the design window. *For
example, I can design a query and run it or view the SQL or see it in
design mode.

Have you compacted the database?

#4

This is a strange problem.

You may have to export all objects into a new clean database

For FREE Access ebook and videos click here
http://access-databases.com/ebook

On 22 Jan, 19:03, Sheldon <SHPsalm... (AT) aol (DOT) com> wrote:

Quote:

Is there a way to preventing Access from automatically rewriting a
query? *I wrote an update query to update five fields if any of four
fields in a record have a null. *It runs fine but as soon as I save,
quit out of query and reopen it, the query has been rewritten to
something incredibly complex with about 200 rows that I don't even
know how to delete. *It still runs correctly but Is there a way to
keep the query looking like how I wrote it? *Perhaps Access sometimes
rewrites queries for optimization but it doesn't run any faster than
2-3 seconds but now it's impossible to update and maintain. *Thanks
for any insight into this.

Sheldon Potolsky

#5

When you work in design view of a query, Access automatically figures out
the SQL. Therefore, *any* change you make in design view, will cause Access
to modify the SQL according to the algorithms Microsoft built into it, and
so your neatly formatted SQL statement will be replaced.

You can't change that behavior, so you will be looking for ways to work
around it. One approach is to create a table with a memo field, so you can
store your SQL statements in there. Another is to create a VBA function with
the SQL statement as a string variable, so the code will Execute the string.
Both are appropriate at different times.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sheldon" <SHPsalm139 (AT) aol (DOT) com> wrote

This occurs whether I'm in the design or SQL window. I compact the
database regularly but that has no effect on the unwanted behavior I'm
seeing.

Sheldon

On Jan 22, 2:28 pm, Salad <o... (AT) vinegar (DOT) com> wrote:

Quote:

Are you entering the SQL window instead of the design window. For
example, I can design a query and run it or view the SQL or see it in
design mode.

Have you compacted the database?

#6

On 22 Sty, 20:03, Sheldon <SHPsalm... (AT) aol (DOT) com> wrote:

Quote:

Is there a way to preventing Access from automatically rewriting a
query? *I wrote an update query to update five fields if any of four
fields in a record have a null. *It runs fine but as soon as I save,
quit out of query and reopen it, the query has been rewritten to
something incredibly complex with about 200 rows that I don't even
know how to delete. *It still runs correctly but Is there a way to
keep the query looking like how I wrote it? *Perhaps Access sometimes
rewrites queries for optimization but it doesn't run any faster than
2-3 seconds but now it's impossible to update and maintain. *Thanks
for any insight into this.

Sheldon Potolsky

Can you show us your sql statement ?
I think you have problem with WHERE section.
If you have some OR's (in parenthesis) joined with AND's, access
rewrites it to AND's joined with OR's.
There are some tricks to prevent it.

--
KN

#7

I'd had a number of OR conditions but I've subsequently rewritten the
query and the behavior I was experiencing is no longer there. I do
appreciate the help and suggestions though. Thank you!

Sheldon

On Jan 22, 9:15*pm, "Allen Browne" <AllenBro... (AT) SeeSig (DOT) Invalid> wrote:

Quote:

When you work in design view of a query, Access automatically figures out
the SQL. Therefore, *any* change you make in design view, will cause Access
to modify the SQL according to the algorithms Microsoft built into it, and
so your neatly formatted SQL statement will be replaced.

You can't change that behavior, so you will be looking for ways to work
around it. One approach is to create a table with a memo field, so you can
store your SQL statements in there. Another is to create a VBA function with
the SQL statement as a string variable, so the code will Execute the string.
Both are appropriate at different times.

--
Allen Browne - Microsoft MVP. *Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sheldon" <SHPsalm... (AT) aol (DOT) com> wrote in message

news:52d0c44d-4d48-4af8-9c38-32f8d958fd41 (AT) s36g2000vbp (DOT) googlegroups.com...
This occurs whether I'm in the design or SQL window. *I compact the
database regularly but that has no effect on the unwanted behavior I'm
seeing.

Sheldon

On Jan 22, 2:28 pm, Salad <o... (AT) vinegar (DOT) com> wrote:

Are you entering the SQL window instead of the design window. For
example, I can design a query and run it or view the SQL or see it in
design mode.

Have you compacted the database?- Hide quoted text -

- Show quoted text -

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Preventing Access From Rewriting My Query

comp.databases.ms-access comp.databases.ms-access

Preventing Access From Rewriting My Query - 01-22-2009 , 01:03 PM

Re: Preventing Access From Rewriting My Query - 01-22-2009 , 01:28 PM

Re: Preventing Access From Rewriting My Query - 01-22-2009 , 01:41 PM

Re: Preventing Access From Rewriting My Query - 01-22-2009 , 03:25 PM

Re: Preventing Access From Rewriting My Query - 01-22-2009 , 08:15 PM

Re: Preventing Access From Rewriting My Query - 01-23-2009 , 09:01 AM

Re: Preventing Access From Rewriting My Query - 01-23-2009 , 09:16 AM