Prevent Access screwing up queries it can't understand when switching to design mode

Question

Coming back to Access after a long time doing other stuff, there is one thing that really bugs me, which is that if you unwittingly open a query in Design mode, where the designer can't represent the sql (even if it's valid), the designer will 'correct' your query for you, and there is no undo...

Is there a workaround for this - or an option where I can at least get it to ask me the question first?

(Access 2007)

Answer 1

If you don't like what the Access UI does to your SQL code, why would you even try to view your SQL in it? It wipes any pretty formatting or whitespace that may have been added to aid the human reader. It can even re-factor your SQL: date literals to US format, parens to brackets for a subquery, parameter syntax in a PROCEDURE, loss of column correlation names in a VIEW, etc.

Sounds like a better approach for you is to create database objects using a SQL Data Definition Language (SQL DDL) script then refer to your script for future maintenance.

For example, here's an Access Database Engine SQL DDL script to create some database objects:

CREATE TABLE Constants 
(
 lock CHAR(1) WITH COMPRESSION 
    DEFAULT 'x'
    NOT NULL,
    CONSTRAINT Constants__max_one_row
       CHECK (lock = 'x'), 
 pi DECIMAL(3, 2) NOT NULL
)
;
INSERT INTO Constants (pi) VALUES (3.14)
;
CREATE TABLE Things 
(
 thing_ID CHAR(10) WITH COMPRESSION NOT NULL
    CONSTRAINT uq__Things UNIQUE, 
    CONSTRAINT thing_ID__numeric_chars_only 
       CHECK (thing_ID NOT ALIKE '%[!0-9]%'), 
 thing_name VARCHAR(20) DEFAULT '{{NONE}}' NOT NULL, 
    CONSTRAINT thing_name__whitespace
       CHECK (
              thing_name NOT ALIKE ' %'
              AND thing_name NOT ALIKE '% '
              AND thing_name NOT ALIKE '%  %'
              AND LEN(thing_name) > 0
             ) 
)
;
CREATE PROCEDURE AddThing 
(
 arg_thing_ID CHAR(10), 
 arg_thing_name VARCHAR(20) = '{{NONE}}' 
)
AS 
INSERT INTO Things (thing_ID, thing_name) 
SELECT thing_ID, thing_name
  FROM (
        SELECT RIGHT('0000000000' + arg_thing_ID, 10) AS thing_ID, 
               IIF(LEN(arg_thing_name) = 0, '{{NONE}}', arg_thing_name) 
               AS thing_name      
          FROM Constants
       ) AS DT1
 WHERE thing_ID NOT ALIKE '%[!0-9]%'
       AND thing_name NOT ALIKE ' %'
       AND thing_name NOT ALIKE '% '
       AND thing_name NOT ALIKE '%  %'
;
CREATE VIEW Stuff
(
 stuff_ID, stuff_name
)
AS 
SELECT T1.thing_ID, T1.thing_name
  FROM Things AS T1
 WHERE ' ' & T1.thing_name & ' ' ALIKE '% stuff %'
;

Now I know from experience the Access UI is incapable of exposing some of the feature of the Access Database Engine (CHECK constraints, CHAR() data type, WITH COMPRESSION property, etc) and may try to change the syntax in a Query object's SQL View: parens in the subquery, parameters for the PROCEDURE -- which it will insist on calling a Query --, column correlation name list in for the VIEW -- which it will insist on also calling a Query --, etc). But who cares? If I need to alter the schema I'll do it based on the SQL DDL script and not what the Access UI thinks my script said.

That leaves my free to use the SQL editor of my choice e.g. one that colors keywords separately from data elements, has auto-complete, indents query elements to my choosing, etc.

Answer 2

I am inclined these days to store queries in tables. A form can be used to view the queries and a little code is sufficient to build a query for testing, for example:

 CurrentDb.CreateQueryDef "TempQueryName", Me.SQL

It would, of course, be wise to test first whether the query exists.

You can also DLookUp such a table for SQL to use in code and for RecordSource and Control Source.