Buggy SQL Syntax for External File Path in Access 2007

[Sky]

Access 2007 seems to have buggy behavior with the SQL syntax for an external
file path with brackets. I'm working with Access 2003-compatible .Mdb
databases using Access 2007, if that makes a difference.

To easily reproduce this, here is a simple SQL that uses an external file
path:

SELECT MyField FROM [D:\MyPath\MyData.Mdb].MyTable;

In this example, there is a database named MyData.Mdb in an external path
D:\MyPath, containing a table named MyTable and a field named MyField. You
can easily create a similar query yourself using any front-end database with
any back-end table and path, or even within a single database using any
table and a path to itself.

The above SQL works fine when entered manually into the Access query
designer. It also works in VBA as SQL to open a recordset.

But if you save the query manually, then open it again (or simply create it
in design view and go to SQL view), Access converts the SQL to the
following:

SELECT MyField FROM (D:\MyPath\MyData.Mdb) MyTable;

Notice the brackets have changed to parentheses, and the period separator
has changed to a space. The query still works for the time being, as long as
you make no changes, even though it has a strange SQL syntax that appears to
be an error.

As soon as you make any change to the SQL (such as remove and replace a
character), the query no longer works. You get a message of "Syntax error in
FROM clause". If you use VBA to read the saved QueryDef SQL and try to open
a recordset, you get the same syntax error, because the generated QueryDef
SQL has parentheses. In order to use the SQL, you must convert the "(" back
to "[" and the ") " back to "]." as in the original example.

Manually editing and testing this SQL in the query designer can cause Access
2007 to crash at times. So it seems like a bug. Access 2003 does not do
this.

Even when Access 2007 SQL displays the incorrect parentheses for a saved
query, when I close it and open it in Access 2003 instead, the same query
SQL displays properly with the correct bracket notation. So it appears that
the query is stored properly internally in Jet, but the Access 2007 display
conversion to SQL syntax is incorrect both in the query designer and in the
VBA .SQL property.

For background, I ran into this because I have a complicated multi-table
query, created manually, that uses an external path for comparing
differences with a backup path. Since the backup path can change, I use VBA
to read the SQL and replace the path. However, the stored qdf.SQL causes an
error unless I also convert the erroneous parentheses when using Access 2007
(but not in 2003).

Perhaps this will help someone else who has to work around this problem.

- Steve

 

[Allen Browne]

See if you can work around the problem with:

SELECT MyField FROM MyTable IN 'D:\MyPath\MyData.Mdb';

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

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

Access 2007 seems to have buggy behavior with the SQL syntax for an
external file path with brackets. I'm working with Access 2003-compatible
.Mdb databases using Access 2007, if that makes a difference.

To easily reproduce this, here is a simple SQL that uses an external file
path:

SELECT MyField FROM [D:\MyPath\MyData.Mdb].MyTable;

In this example, there is a database named MyData.Mdb in an external path
D:\MyPath, containing a table named MyTable and a field named MyField. You
can easily create a similar query yourself using any front-end database
with any back-end table and path, or even within a single database using
any table and a path to itself.

The above SQL works fine when entered manually into the Access query
designer. It also works in VBA as SQL to open a recordset.

But if you save the query manually, then open it again (or simply create
it in design view and go to SQL view), Access converts the SQL to the
following:

SELECT MyField FROM (D:\MyPath\MyData.Mdb) MyTable;

Notice the brackets have changed to parentheses, and the period separator
has changed to a space. The query still works for the time being, as long
as you make no changes, even though it has a strange SQL syntax that
appears to be an error.

As soon as you make any change to the SQL (such as remove and replace a
character), the query no longer works. You get a message of "Syntax error
in FROM clause". If you use VBA to read the saved QueryDef SQL and try to
open a recordset, you get the same syntax error, because the generated
QueryDef SQL has parentheses. In order to use the SQL, you must convert
the "(" back to "[" and the ") " back to "]." as in the original example.

Manually editing and testing this SQL in the query designer can cause
Access 2007 to crash at times. So it seems like a bug. Access 2003 does
not do this.

Even when Access 2007 SQL displays the incorrect parentheses for a saved
query, when I close it and open it in Access 2003 instead, the same query
SQL displays properly with the correct bracket notation. So it appears
that the query is stored properly internally in Jet, but the Access 2007
display conversion to SQL syntax is incorrect both in the query designer
and in the VBA .SQL property.

For background, I ran into this because I have a complicated multi-table
query, created manually, that uses an external path for comparing
differences with a backup path. Since the backup path can change, I use
VBA to read the SQL and replace the path. However, the stored qdf.SQL
causes an error unless I also convert the erroneous parentheses when using
Access 2007 (but not in 2003).

Perhaps this will help someone else who has to work around this problem.

 

[Sky]

My simplified example was just a cut-down illustration to reproduce the
Access 2007 bug clearly.

You are correct that for simple single-source queries with file paths, the
IN syntax is stable, and the SQL does not convert erroneously in Access 2007
in my testing. Of course, in such simple cases I usually code the SQL
directly in VBA anyway, without saving a stored query.

In my real queries, I am comparing tables between different databases, with
outer joins on tables having different paths, so the single-source IN syntax
is insufficient.

This file path bug in the SQL is simple enough to code around once you know
it's there. But it seems so obvious a bug I'm surprised that Access 2007
testing did not identify it. I have not seen it in the MS Knowledge Base
online

- Steve

See if you can work around the problem with:

SELECT MyField FROM MyTable IN 'D:\MyPath\MyData.Mdb';

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

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

Access 2007 seems to have buggy behavior with the SQL syntax for an
external file path with brackets. I'm working with Access 2003-compatible
.Mdb databases using Access 2007, if that makes a difference.

To easily reproduce this, here is a simple SQL that uses an external file
path:

SELECT MyField FROM [D:\MyPath\MyData.Mdb].MyTable;

In this example, there is a database named MyData.Mdb in an external path
D:\MyPath, containing a table named MyTable and a field named MyField.
You can easily create a similar query yourself using any front-end
database with any back-end table and path, or even within a single
database using any table and a path to itself.

The above SQL works fine when entered manually into the Access query
designer. It also works in VBA as SQL to open a recordset.

But if you save the query manually, then open it again (or simply create
it in design view and go to SQL view), Access converts the SQL to the
following:

SELECT MyField FROM (D:\MyPath\MyData.Mdb) MyTable;

Notice the brackets have changed to parentheses, and the period separator
has changed to a space. The query still works for the time being, as long
as you make no changes, even though it has a strange SQL syntax that
appears to be an error.

As soon as you make any change to the SQL (such as remove and replace a
character), the query no longer works. You get a message of "Syntax error
in FROM clause". If you use VBA to read the saved QueryDef SQL and try to
open a recordset, you get the same syntax error, because the generated
QueryDef SQL has parentheses. In order to use the SQL, you must convert
the "(" back to "[" and the ") " back to "]." as in the original example.

Manually editing and testing this SQL in the query designer can cause
Access 2007 to crash at times. So it seems like a bug. Access 2003 does
not do this.

Even when Access 2007 SQL displays the incorrect parentheses for a saved
query, when I close it and open it in Access 2003 instead, the same query
SQL displays properly with the correct bracket notation. So it appears
that the query is stored properly internally in Jet, but the Access 2007
display conversion to SQL syntax is incorrect both in the query designer
and in the VBA .SQL property.

For background, I ran into this because I have a complicated multi-table
query, created manually, that uses an external path for comparing
differences with a backup path. Since the backup path can change, I use
VBA to read the SQL and replace the path. However, the stored qdf.SQL
causes an error unless I also convert the erroneous parentheses when
using Access 2007 (but not in 2003).

Perhaps this will help someone else who has to work around this problem.

 

[RD]

Access' query grid does do strange things for no apparent (at least to
me) reason. I'm using Acc2K3.

On the Access Web, under Queries is the article:
Getting a related field from a GroupBy (total) query

The last query looks like this:
SELECT Q.BookID, Q.BorrowerID
FROM TableName As Q INNER JOIN
(SELECT BookID, Max(DateOut) As S
FROM TableName
GROUP BY BookID) As T
ON Q.BookId=T.BookId AND Q.DateOut = T.S

Entering that into the grid, saving it and re-opening it produces
this:
SELECT Q.BookID, Q.BorrowerID
FROM TableName AS Q INNER JOIN [SELECT BookID, Max(DateOut) As S
FROM TableName
GROUP BY BookID]. AS T ON (Q.DateOut = T.S) AND
(Q.BookId=T.BookId);

Parentheses around the sub-select are turned into square brackets and
a dot is appended to the sub-select. As you pointed out, the query
seems to run fine unless you make a change and then you get a syntax
error.

I've never seen anything about this glitch anywhere.

Hoping someone at least has an explanation,
RD

Access 2007 seems to have buggy behavior with the SQL syntax for an external
file path with brackets. I'm working with Access 2003-compatible .Mdb
databases using Access 2007, if that makes a difference.

To easily reproduce this, here is a simple SQL that uses an external file
path:

SELECT MyField FROM [D:\MyPath\MyData.Mdb].MyTable;

In this example, there is a database named MyData.Mdb in an external path
D:\MyPath, containing a table named MyTable and a field named MyField. You
can easily create a similar query yourself using any front-end database with
any back-end table and path, or even within a single database using any
table and a path to itself.

The above SQL works fine when entered manually into the Access query
designer. It also works in VBA as SQL to open a recordset.

But if you save the query manually, then open it again (or simply create it
in design view and go to SQL view), Access converts the SQL to the
following:

SELECT MyField FROM (D:\MyPath\MyData.Mdb) MyTable;

Notice the brackets have changed to parentheses, and the period separator
has changed to a space. The query still works for the time being, as long as
you make no changes, even though it has a strange SQL syntax that appears to
be an error.

As soon as you make any change to the SQL (such as remove and replace a
character), the query no longer works. You get a message of "Syntax error in
FROM clause". If you use VBA to read the saved QueryDef SQL and try to open
a recordset, you get the same syntax error, because the generated QueryDef
SQL has parentheses. In order to use the SQL, you must convert the "(" back
to "[" and the ") " back to "]." as in the original example.

Manually editing and testing this SQL in the query designer can cause Access
2007 to crash at times. So it seems like a bug. Access 2003 does not do
this.

Even when Access 2007 SQL displays the incorrect parentheses for a saved
query, when I close it and open it in Access 2003 instead, the same query
SQL displays properly with the correct bracket notation. So it appears that
the query is stored properly internally in Jet, but the Access 2007 display
conversion to SQL syntax is incorrect both in the query designer and in the
VBA .SQL property.

For background, I ran into this because I have a complicated multi-table
query, created manually, that uses an external path for comparing
differences with a backup path. Since the backup path can change, I use VBA
to read the SQL and replace the path. However, the stored qdf.SQL causes an
error unless I also convert the erroneous parentheses when using Access 2007
(but not in 2003).

Perhaps this will help someone else who has to work around this problem.

- Steve

 

[David W. Fenton]

For background, I ran into this because I have a complicated
multi-table query, created manually, that uses an external path
for comparing differences with a backup path. Since the backup
path can change, I use VBA to read the SQL and replace the path.
However, the stored qdf.SQL causes an error unless I also convert
the erroneous parentheses when using Access 2007 (but not in
2003).

I've used that method to get data from multiple databases into a
single query, too, and it's one of the great things about Jet SQL.

I assume you've patched to the latest versions of MS Access and
trolled the Knowledge Base to see if it's a reported bug, so I can
only suggest kludgy workarounds:

1. save a query for each individual table using the simple FROM IN
'' syntax that still works, OR

2. write your FROM clause with derived tables that do that:

FROM
[SELECT Table1.* FROM Table1 IN 'c:\MyDatabase.mdb']. As Table1
JOIN
[SELECT Table2.* FROM Table2 IN 'c:\MyOtherDatabase.mdb']. As
Table2
ON Table1.ID = Table2.ID

Messy, messy, messy (and could be less so if you're using ANSI 92
mode, since you can use () in place of }{ and get rid of the
trailing "." for the right bracket), but I think it would allow you
to work with your SQL without having these problems until MS fixes
the problem.

 

[David W. Fenton]

Access' query grid does do strange things for no apparent (at
least to me) reason. I'm using Acc2K3.

On the Access Web, under Queries is the article:
Getting a related field from a GroupBy (total) query

The last query looks like this:
SELECT Q.BookID, Q.BorrowerID
FROM TableName As Q INNER JOIN
(SELECT BookID, Max(DateOut) As S
FROM TableName
GROUP BY BookID) As T
ON Q.BookId=T.BookId AND Q.DateOut = T.S

Entering that into the grid, saving it and re-opening it produces
this:
SELECT Q.BookID, Q.BorrowerID
FROM TableName AS Q INNER JOIN [SELECT BookID, Max(DateOut) As S
FROM TableName
GROUP BY BookID]. AS T ON (Q.DateOut = T.S) AND
(Q.BookId=T.BookId);

Parentheses around the sub-select are turned into square brackets
and a dot is appended to the sub-select. As you pointed out, the
query seems to run fine unless you make a change and then you get
a syntax error.

I've never seen anything about this glitch anywhere.

Hoping someone at least has an explanation,

[]. AS Alias is Jet SQL's syntax for derived tables. Standard ANSI
syntax is () As Alias, and when you type that in, you find that
Access converts it to Jet's syntax.

This is a good thing, seems to me.

And I've used it for years (I do lots of SQL with derived tables,
though mostly not in saved queries).

One option you might consider is setting your database to use ANSI
92 SQL, which will mean that you can use the standard () AS Alias
syntax. But it also means you have to use ANSI wildcards, % in place
of * and _ in place of ?.

Now, as to your other assertion, that the SQL breaks if you edit it,
I've never had that issue myself. Perhaps your edit is in error?
It's certainly the case that the bracket syntax means you can't have
any brackets inside the SELECT statement of your derived table, so
if you're introducing the need for brackets (e.g., by adding a field
to the SELECT clause that has a space or non-standard character in
its name), then that would definitely break it. If you've got
fields/tables with badly-designed names (i.e., with spaces and
non-alphanumeric characters) then you're going to have to use
brackets and in that case, ANSI 92 mode is probably going to make
life easier for working with derived tables (though it means
converting all the wildcards...).

But the better alternative is to NOT USE STUPID NAMES for fields and
tables. That means no spaces and no funny characters.

Of course, often you have no control over the schema so you're
forced to use badly-named fields/tables. In that case, I'd likely
create saved queries that alias the fields to good names, or if
using SQL Server, create a server-side view with the aliases and use
that in place of the basic table. That way, you don't have to worry
about mucking around with ANSI 92 mode and finessing issues with
brackets in SQL derived tables.

 

[david]

Now, as to your other assertion, that the SQL breaks if you edit it,

I've never had that issue myself. Perhaps your edit is in error?

Known problem though. If you edit the SQL, Access will re-arrange
it on save. Normally this just means re-arranging the logic in your
Where clause, but in odd edge cases like this, the new SQL is broken.
Also known to affect ODBC connections with ODBC bugs and
Grouping with sort/group bugs.

(david)

Access' query grid does do strange things for no apparent (at
least to me) reason. I'm using Acc2K3.

On the Access Web, under Queries is the article:
Getting a related field from a GroupBy (total) query

The last query looks like this:
SELECT Q.BookID, Q.BorrowerID
FROM TableName As Q INNER JOIN
(SELECT BookID, Max(DateOut) As S
FROM TableName
GROUP BY BookID) As T
ON Q.BookId=T.BookId AND Q.DateOut = T.S

Entering that into the grid, saving it and re-opening it produces
this:
SELECT Q.BookID, Q.BorrowerID
FROM TableName AS Q INNER JOIN [SELECT BookID, Max(DateOut) As S
FROM TableName
GROUP BY BookID]. AS T ON (Q.DateOut = T.S) AND
(Q.BookId=T.BookId);

Parentheses around the sub-select are turned into square brackets
and a dot is appended to the sub-select. As you pointed out, the
query seems to run fine unless you make a change and then you get
a syntax error.

I've never seen anything about this glitch anywhere.

Hoping someone at least has an explanation,

[]. AS Alias is Jet SQL's syntax for derived tables. Standard ANSI
syntax is () As Alias, and when you type that in, you find that
Access converts it to Jet's syntax.

This is a good thing, seems to me.

And I've used it for years (I do lots of SQL with derived tables,
though mostly not in saved queries).

One option you might consider is setting your database to use ANSI
92 SQL, which will mean that you can use the standard () AS Alias
syntax. But it also means you have to use ANSI wildcards, % in place
of * and _ in place of ?.

Now, as to your other assertion, that the SQL breaks if you edit it,
I've never had that issue myself. Perhaps your edit is in error?
It's certainly the case that the bracket syntax means you can't have
any brackets inside the SELECT statement of your derived table, so
if you're introducing the need for brackets (e.g., by adding a field
to the SELECT clause that has a space or non-standard character in
its name), then that would definitely break it. If you've got
fields/tables with badly-designed names (i.e., with spaces and
non-alphanumeric characters) then you're going to have to use
brackets and in that case, ANSI 92 mode is probably going to make
life easier for working with derived tables (though it means
converting all the wildcards...).

But the better alternative is to NOT USE STUPID NAMES for fields and
tables. That means no spaces and no funny characters.

Of course, often you have no control over the schema so you're
forced to use badly-named fields/tables. In that case, I'd likely
create saved queries that alias the fields to good names, or if
using SQL Server, create a server-side view with the aliases and use
that in place of the basic table. That way, you don't have to worry
about mucking around with ANSI 92 mode and finessing issues with
brackets in SQL derived tables.

 

[RD]

On 6 Mar 2009 23:55:33 GMT, "David W. Fenton"

Hoping someone at least has an explanation,

[]. AS Alias is Jet SQL's syntax for derived tables. Standard ANSI
syntax is () As Alias, and when you type that in, you find that
Access converts it to Jet's syntax.

This is a good thing, seems to me.

And I've used it for years (I do lots of SQL with derived tables,
though mostly not in saved queries).

One option you might consider is setting your database to use ANSI
92 SQL, which will mean that you can use the standard () AS Alias
syntax. But it also means you have to use ANSI wildcards, % in place
of * and _ in place of ?.

Now, as to your other assertion, that the SQL breaks if you edit it,
I've never had that issue myself. Perhaps your edit is in error?
It's certainly the case that the bracket syntax means you can't have
any brackets inside the SELECT statement of your derived table, so
if you're introducing the need for brackets (e.g., by adding a field
to the SELECT clause that has a space or non-standard character in
its name), then that would definitely break it. If you've got
fields/tables with badly-designed names (i.e., with spaces and
non-alphanumeric characters) then you're going to have to use
brackets and in that case, ANSI 92 mode is probably going to make
life easier for working with derived tables (though it means
converting all the wildcards...).

But the better alternative is to NOT USE STUPID NAMES for fields and
tables. That means no spaces and no funny characters.

Of course, often you have no control over the schema so you're
forced to use badly-named fields/tables. In that case, I'd likely
create saved queries that alias the fields to good names, or if
using SQL Server, create a server-side view with the aliases and use
that in place of the basic table. That way, you don't have to worry
about mucking around with ANSI 92 mode and finessing issues with
brackets in SQL derived tables.

Thanks for that. (Been on vacation, sorry for delay)

I didn't know that about derived tables. I should probably delve a
little deeper into Jet.

I've thought about using ANSI 92, just haven't moved forward with it.
I rarely use wildcards, if I can help it.

Lol about the STUPID NAMES. I use perfectly good names in the
databases I design. Unfortunately, most of the databases I support
were created by a complete idiot (and she was well paid). I've
considered posting some of what I find on the Daily WTF but there are
too many snobs there that would reply, "TRWTF is Access!"

I often use intermediate queries to rename fields into someting sane.

Anyway, thanks.

RD

 

[RD]

Known problem though. If you edit the SQL, Access will re-arrange
it on save. Normally this just means re-arranging the logic in your
Where clause, but in odd edge cases like this, the new SQL is broken.
Also known to affect ODBC connections with ODBC bugs and
Grouping with sort/group bugs.

(david)

Exactly! That's why I notice it only occasionally.

Thanks,
RD