Stop access from changing SQL queries

[hawaiijeff]

Is there any way to stop access (Access 2000) from changing the syntax of my SQL queries? For example, I don't always put the table_name.[field] because it makes it hard to read. Access doesn't have a problem running the query and letting me return to the same SQL view right after that. But when I save the query, close it, and open it up again it has made all sorts of modifications that make it hard for me to read. The other thing that it does is run all the code together as one long string rather than putting each field that I select on a separate line like I do for readability. Maybe I'm asking too much of access. Thanks.

[JustJim]

Is there any way to stop access (Access 2000) from changing the syntax of my SQL queries? For example, I don't always put the table_name.[field] because it makes it hard to read. Access doesn't have a problem running the query and letting me return to the same SQL view right after that. But when I save the query, close it, and open it up again it has made all sorts of modifications that make it hard for me to read. The other thing that it does is run all the code together as one long string rather than putting each field that I select on a separate line like I do for readability. Maybe I'm asking too much of access. Thanks.

When you store a query in Access, it is analysed and optimised for you. See here for why this is a good thing. As you point out, it does take away some of the simplifications that you are using, but I suspect that it does this for reasons of eliminating possible ambiguities. You'll get used to reading the table names as part of the SQL.

Jim

[hawaiijeff]

I feared this would be the answer. It seems like there should be an option to turn it off for instances when time (or processing power or memory) is not a critical factor. For example, I'm dealing with a relatively small database and if the query takes twice as long as normal, that's pretty irrelevant to me. A half second or one second really doesn't matter. I thought that I remember that Oracle had a way that you could turn off the optimizer. I guess that's what I'm really looking for here. Thanks for that information.

[JustJim]

I feared this would be the answer. It seems like there should be an option to turn it off for instances when time (or processing power or memory) is not a critical factor. For example, I'm dealing with a relatively small database and if the query takes twice as long as normal, that's pretty irrelevant to me. A half second or one second really doesn't matter. I thought that I remember that Oracle had a way that you could turn off the optimizer. I guess that's what I'm really looking for here. Thanks for that information.

No worries Mate.
'AveAGoodWeekend

Jim

[NeoPa]

I too find that irritating.
SQL is so much harder to work with when it's been Accessified :(