Inconsistent square bracket in Query SQL

[Christine]

It has come to my attention that sometimes, when I open a Query in SQL
View, the SQL that I see is not exactly the same as the SQL in the
Query's Querydef. The difference I see (only occasionally) has to do
with square brackets and dot on some tblName.FieldName portions of the
query.

The following routine demonstrates how I see the QueryDef's SQL:
Sub DbgQuery(sQryName As String)
'' Prints the SQL for the given query to the Debug Window
Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.QueryDefs(sQryName)
Debug.Print qdf.SQL
Set qdf = Nothing
End Sub

I have some VBA code that compares queries of the same name accross
different mdb's and have come across some instances where the SQL is
identical when I view it in the SQL View of the Query, but my Compare
code is picking up differences. In each of these cases, the VBA is
showing extra brackets, but I cannot see it in the SQL View and thus
they look the same.

Has anyone ever observed this before? Is there any way I can get
'identical Queries' to look the same via VBA? I am so surprised that
the SQL View does not match the QueryDef.SQL. I've been comparing
queries across mdbs like this for months and had not seen this before,
and now suddenly, four or five of them have developed differences as a
result of the brackets! I am quite dependent on my compare technique
and this new quirk is posing me a real challenge.

Any comments or insights greatly appreciated.
TIA,
Christine

 

[Allen Browne]

Hi Christine.

I've never seen this. If you have not already done so, uncheck the boxes
under:
Tools | Options | General | Name AutoCorrect
Access will add square brackets around a field name that requires it (e.g.
begins with a number, or contains a space), so I am wondering whether Name
AutoCorrect thinks it's safer to add the square brackets. After turning this
off, compact the database, then open the query in SQL View, change the text
and save.

If that does not fix the problem, please post back with more info. The only
issue I've seen with comparing the SQL of the QueryDef is that it tends to
have a trailing CrLf.

 

[Ken Snell [MVP]]

PMFJI...

Perhaps this is the redefinition by ACCESS of the subquery to the
[subquery]. syntax?

Access translates FROM-clause subqueries in the standard parenthetical form
into the "[subquery]. " form, and any square bracket inside the subquery's
enclosing brackets will interfere with the correct interpretation of it.
Even a bracketed

.[field] reference will cause the problem. I know
of no way around this but to rewrite the query to avoid the use of a FROM
subquery.

 

[Allen Browne]

Ken, thanks for that.

Can you give an example of where JET might do this?

 

[Graham R Seach]

Ken's right; Access does this all the time. I've experienced the behaviour
many times, because I build a lot of nested subqueries.

To resolve the issue, replace the square brackets with round ones, and
remove the dot.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

PMFJI...

Perhaps this is the redefinition by ACCESS of the subquery to the
[subquery]. syntax?

Access translates FROM-clause subqueries in the standard parenthetical form
into the "[subquery]. " form, and any square bracket inside the subquery's
enclosing brackets will interfere with the correct interpretation of it.
Even a bracketed

.[field] reference will cause the problem. I know
of no way around this but to rewrite the query to avoid the use of a FROM
subquery.


--

Ken Snell
<MS ACCESS MVP>

Hi Christine.

I've never seen this. If you have not already done so, uncheck the boxes
under:
Tools | Options | General | Name AutoCorrect
Access will add square brackets around a field name that requires it (e.g.
begins with a number, or contains a space), so I am wondering whether Name
AutoCorrect thinks it's safer to add the square brackets. After turning this
off, compact the database, then open the query in SQL View, change the text
and save.

If that does not fix the problem, please post back with more info. The only
issue I've seen with comparing the SQL of the QueryDef is that it tends to
have a trailing CrLf.

 

[Ken Snell]

Whenever I try to use a subquery as a FROM table, Jet does this after saving
and closing the query. I get around it by rewriting the query in some way to
use a subquery as the list of values in an
WHERE field IN (subquery)
syntax or something else.

--

Ken Snell
<MS ACCESS MVP>

Ken, thanks for that.

Can you give an example of where JET might do this?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

PMFJI...

Perhaps this is the redefinition by ACCESS of the subquery to the
[subquery]. syntax?

Access translates FROM-clause subqueries in the standard parenthetical
form
into the "[subquery]. " form, and any square bracket inside the subquery's
enclosing brackets will interfere with the correct interpretation of it.
Even a bracketed

.[field] reference will cause the problem. I know
of no way around this but to rewrite the query to avoid the use of a FROM
subquery.

 

[Christine]

Many thanks to Allen, Ken & Graham for your interest and input. I
think Allen's idea of my problem being related to AutoCorrect issues
is the most likely. I was not exactly able to fix the problem by
following Allen's recommendation but it got me looking further into
the DAO.queryDef properties. I was hoping for some other SQL like
prperty that would match what I see in the interactive SQL View, but
no such luck.

However, I did discover the DOL property and see significant
differences there that presumably could have been applied (by
Access/Jet?) to the interactive SQL View SQL to produce the
Querdef.SQL that I get via VBA/DAO. In looking through the newsgroups,
I did discover snippets tying the DOL prepoerty to the AutoCorrect
option. Allen, I believe you were involved in those threads, in fact I
tried following a link to a web page of yours that was supposed to
give more info on (I think) some of the hazards of Autocorrect) but it
was a broken link.

The queries in question did not have any subqueries but they did
involve tables whose structures had been changing lately, so that is
probably why their DOL's had been touched. As a result of each of your
inputs, I was able to follow up with more investigation today, and at
least now feel more comfortale and educated about AutoCorrect and
other changes that Access will make to my queries and not as anxious
that suddenly all my queries are going to just start changing
themselves out of the blue, and totally destroy my Compare technique.

I'm not yet sure that I want to turn off Name AutoCorrect alltogether
for ever. Allen, if you should happen to refresh that link to your web
site that sheds more light on this, I'd appreciation knowing about it.

So a big thank-you to you all - how great to get so many educated and
well-informed reponses overnight. It really turns our time difference
into a huge plus. And turned my despair & disbelief as of late
yesterday to a manageable and explorable issue today.

Many Thanks & Regards,
Christine

 

[Allen Browne]

Hi Christine

My web host was off-line for a couple of hours. The page should be availabe
again now. It's:
http://members.iinet.net.au/~allenbrowne/bug-03.html
You may be surprised how many problems it causes.

I have not experienced Access stuffing up the QueryDef SQL based on Name
AutoCorrect, but Ken and Graham have clearly experienced the issue and
identified a cause. So, if you have a subquery in the FROM clause, follow
their advice to work around this situation. If you do not have a subquery in
the FROM clause, the issue may relate to some of the other things you
raised.

For anyone who is not sure what a subquery is, see:
http://support.microsoft.com/?id=209066

 

[Christine]

Thanks Allen,
Your web page on the hazards of Name AutoCorrect was very
informative. I am now convinced and will go through the process of
turning it off in all my mdbs.

I have experienced the very unpleasant situation in the past of Access
shutting down when I was in the process of changing field names in
tables and trying to open queries. At the time, I did not know what on
earth was going on and it was a huge battle to get Access to stay open
long enough for me to be able to correct the situation. One particular
situation was just dreadful - an absolute nightmare. So 1) I am very
pleased to understand what was really going on and 2) avoiding that
from ever happening again is more than enough of an argument to turn
it off.

Thanks so much for your gracious help.
Best Regards,
Christine