How to stop MS Access from changing my SQL code?
Here's a dirty trick : append an UNION
at the end of the query which is always FALSE.
SELECT field_1, field_2
FROM my_table
UNION select field_1, field_2 FROM my_table WHERE False = True;
That's horrible and I'm ashamed to do something like that but it works.
There's no stopping Access from changing your SQL if you save it as a QueryDef object (ie, using the graphical query editor). You have (at least) two other options:
- Build your queries in VBA
- Store your queries in a table dedicated to queries using a Memo field to store the SQL (this will also require some VBA to take the SQL and execute it or assign it to a temporary querydef, etc.)
You can still use the QBE (query-by-example) window to generate your SQL initially if you want.
Also, if you have a non-Jet backend (MS SQL Server, for example) you can write pass-through queries. You lose the graphical interface but gain all of the functionality of writing SQL in your backend of choice. Access won't rearrange the formatting on pass-through queries.