Is there a fix for Access2003 rewriting (xxx) as [xxx]. ?

P

Peter Facey

I have the same problem mentioned in 2005 in this thread:
https://www.microsoft.com/office/co...fc-b776-ff82b9e99d87&sloc=en-us&wa=wsignin1.0

It seems that when one closes a perfectly legal SQL query that works ok,
Access rewrites it into a form which Access will itself reject the next time
the query is opened and closed in design view.

For example, this query
SELECT COUNT(*)
FROM (SELECT DISTINCT Addr1,Addr2,Addr3,Addr4,Addr5,Addr6
FROM Voters INNER JOIN BartonFarmPostcodes AS BF ON
[Voters].[Postcode]=BF.Postcode) AS QQ;

will be rewritten as
SELECT COUNT(*)
FROM [SELECT DISTINCT Addr1,Addr2,Addr3,Addr4,Addr5,Addr6
FROM Voters INNER JOIN BartonFarmPostcodes AS BF ON
[Voters].[Postcode]=BF.Postcode]. AS QQ;

It will continue to work. But if you open it again in design view, make a
trivial change so that Access needs to reprocess it, and then close it, you
get the error message SYNTAX ERROR IN FROM CLAUSE with a blinking cursor on
the equals sign.

One can solve this by changing the square brakets back to round and deleting
the dot. However, this inability to resave queries interferes with the View
Object Dependencies feature, because Access cannot rebuild the dependency
information and says "could not enable name Autocorrect for one or more
objects because Access could not open or save the objects".

This incorrect rewriting of SQL queries is a long standing bug in
Access2003. Is there a fix for it? Mine is SP3.
 
J

Jerry Whittle

1. Once you create a query in SQL View and like how it looks, never open it
in Design View again.

2. Turn off Name Autocorrect on your databases. It's more trouble than it's
worth as it cause performance, and other, problems. I use Rich Fisher's Find
and Replace when I need to change a table or field name.

3. You could write the SQL statement in a code module and execute it there.
Access won't mess with code the same way. One problem would be that a SQL
statement in code won't be optimized like a normal query or record source in
a form or report.
 
B

Bob Barrows

S

Sylvain Lafontaine

The syntax [xxx]. is the official syntax used by JET for a subquery - not
the parenthesis - so I don't see how you could stop Access of converting
your (xxx) to [xxx]. if you are using the Graphical Designer.

Access is also prone to core dumping or to bug whenever you start using
complex statements involving subqueries; so what you are encountering is not
surprising.

The first thing would be to stop using both the Graphical Designer and the
Autocorrect feature. Another possibility would be to make some change to
your query, for example replacing the DISTINCT with a GROUP BY or to give an
alias to the Voters table or to give a name to the column Count(*); for
example:

SELECT COUNT(*) as C
FROM [SELECT Addr1,Addr2,Addr3,Addr4,Addr5,Addr6
FROM Voters as V INNER JOIN BartonFarmPostcodes AS BF ON
V.Postcode=BF.Postcode Group By Addr1,Addr2,Addr3,Addr4,Addr5,Addr6]. AS QQ;

You can also try to put the subquery into its own QueryDef or View.
Sometimes, making a small change like this will help. However, if I were
you, I would have stopped using the Graphical Designer and the Autocorrect
feature a long time ago.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Peter Facey said:
I have the same problem mentioned in 2005 in this thread:
https://www.microsoft.com/office/co...fc-b776-ff82b9e99d87&sloc=en-us&wa=wsignin1.0

It seems that when one closes a perfectly legal SQL query that works ok,
Access rewrites it into a form which Access will itself reject the next
time
the query is opened and closed in design view.

For example, this query
SELECT COUNT(*)
FROM (SELECT DISTINCT Addr1,Addr2,Addr3,Addr4,Addr5,Addr6
FROM Voters INNER JOIN BartonFarmPostcodes AS BF ON
[Voters].[Postcode]=BF.Postcode) AS QQ;

will be rewritten as
SELECT COUNT(*)
FROM [SELECT DISTINCT Addr1,Addr2,Addr3,Addr4,Addr5,Addr6
FROM Voters INNER JOIN BartonFarmPostcodes AS BF ON
[Voters].[Postcode]=BF.Postcode]. AS QQ;

It will continue to work. But if you open it again in design view, make a
trivial change so that Access needs to reprocess it, and then close it,
you
get the error message SYNTAX ERROR IN FROM CLAUSE with a blinking cursor
on
the equals sign.

One can solve this by changing the square brakets back to round and
deleting
the dot. However, this inability to resave queries interferes with the
View
Object Dependencies feature, because Access cannot rebuild the dependency
information and says "could not enable name Autocorrect for one or more
objects because Access could not open or save the objects".

This incorrect rewriting of SQL queries is a long standing bug in
Access2003. Is there a fix for it? Mine is SP3.
Click to expand...
 
P

Peter Facey

Thanks. I may have mis-stated what I do. I am using SQL view not Design View.
That is, having opened a database, I choose Queries, click on an existing
query, then click Design and the query opens in SQL View. I then edit it,
say, and close it.
 
P

Peter Facey

Thanks. Those are workarounds really. So I take it that the answer is 'no'
microsoft still hasn't fixed this. Is this problem present in Access 2007?
 
B

Bob Barrows

Peter said:
Thanks. I may have mis-stated what I do. I am using SQL view not
Design View. That is, having opened a database, I choose Queries,
click on an existing query, then click Design and the query opens in
SQL View. I then edit it, say, and close it.
Click to expand...

The only time Access revises my sql is if I close the query while in
Design view. I have not experienced what you are seeing
 
P

Peter Facey

Thanks. Yes, those are possible workarounds. But I'm just revisiting an old
database of mine that has 366 queries in it (only a minority of which raise
this problem, and many of which are probably obsolete). On graphical design,
I've actually never used it. I just thought a feature like View/Object
Dependencies would help me remember what the heck's going on in this database!
 
S

Sylvain Lafontaine

Maybe you could make copy of the database with the Name Autocorrect feature
ON?

Also, I didn't tried but maybe you could try leaving the "Track name
Autocorrect info" to ON and only set the "Perform name AutoCorrect" to OFF
instead of setting both to OFF?
 
Top