DESIGN mode changes SQL query

[flyagain]

I have a query designed in Access SQL mode. It contains a series of: where ... and where...this or where...this and this and this. The query runs correctly. If I then use Access DESIGN mode to change a criteria value, the query fails to run correctly. Looking at the SQL, it has been totally changed by Access. Is there a way of stopping Access from changing the SQL itself when using the DESIGN mode to change a parameter? (I can change the criteria in SQL mode but this is for a customer who likes DESIGN mode)

 

[scriverb]

SQL code can be changed bia VBA code:

Dim db as DAO.Database
Set db = Currentdb
db.QueryDefs("[i][red]yourqueryname[/red][/i]").SQL = "Select " & _
A.* FROM yourtablename as A WHERE A.[fieldname] = 123 " & _
"Order By A.[Fieldname];"
db.close

This is just an example of how to modify your SQL with VBA code. You can cancatenate form control values, function calls, and change any of the queries code with this process.

Post back if you have any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[jksmi]

Hi,

why is the client changing your queries? if it is to change a parameter does it have to be stored in the view or could the user input it when they run the query,

i.e. instead of
WHERE [SALES_VALUE)>20000
you could use
WHERE [SALES_VALUE]>[Please enter sales value > qualification...]

or, if the expressions are constant (always > or =, etc) could you allow them to change the values of a table that the queries use to look up the parameter table?

HTH, Jamie
FAQ219-2884

 

[flyagain]

The queries are actually the client's, not mine. When we added some tables to the db, we were asked for help in incorporating them into some existing queries. They were already using DESIGN mode to create and change criteria for queries. That is when we found that the sql was being changed incorrectly when using that mode. Has anyone experience with this situation?

 

[scriverb]

You MUST keep the end users out of the design windows!!!!





I am saying this with a passsion. Reprogram this database to NEVER allow the users to get at the code, and the query design window is code. You can design the queries to use forms and prompts to the user to allow them to select specific data from the tables. This can all be done with ACCESS.

Prompt back with an example of what you want to do in a query where the user is involved in changing it or in otherwords modifying the selection criteria. We can post some code to show you how to do this.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[flyagain]

Though the advice may be valid, it does not answer the question. I already know how to devise parameter queries, etc. so I won't ask for that. I just wanted to know about the why's and how's of DESIGN mode vs. SQL mode for a query.

By the way, one of the reasons that the client chose Access as a frontend was to be able to do some of their own queries.

 

[scriverb]

I have seen SQL code that when the window is changed to the QDW ACCESS makes changes but usually these are to more effecient. They really never change the outcome. Can you give an example of this query so that I can try it here. I am just curious as I don't want to run into the same thing.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[jksmi]

Hi,

The only time that I have come across Access actually changing SQL that I have entered it either replaced brackets () around a subquery with []. or add brackets to the WHERE or HAVING statements.

I have not found anyways to prevent Access doing this (other than using stored views rather than sub queries), it is just Access' implementation of ANSI SQL, which it will apply to any query opened, edited and saved in design view rather than SQL view. SQL view will allow you to type SQL directly into Access, but even so, Access doesnt fully support ANSI SQL.

If the client wants to edit existing queries using the graphical editor then you perhaps you shouldnt be integrating your tables directly into their queries, but create views that integrate the new tables with the old and point their queries to these new views (i.e. another layer but far simpler for the client).

HTH, Jamie
FAQ219-2884