Access doesn't like its own SQL

[Guest]

Here's a weird one. The code

Set qy = CurrentDb.QueryDefs(strSourceQuery)
strSQL = qy.SQL

Set qy = CurrentDb.QueryDefs("Kits Found Special")
qy.SQL = strSQL

produces error 3131: "Syntax error in FROM clause", despite the fact that
the 1st query works fine if run from the database window.

After the 2nd line, strSQL is

"SELECT [Sub Kit Comps].KitItem
FROM [SELECT [Sub Kit Comps].KitItem, [Sub Kit Comps].ITEM, [Sub Kit
Comps].PER, [Sub Kit Comps_1].ITEM, [Sub Kit Comps_1].PER FROM [Sub Kit
Comps] INNER JOIN [Sub Kit Comps] AS [Sub Kit Comps_1] ON [Sub Kit
Comps].KitItem = [Sub Kit Comps_1].KitItem WHERE ((([Sub Kit
Comps].ITEM)='600-211-1231') AND (([Sub Kit Comps].PER)=4) AND (([Sub Kit
Comps_1].ITEM)='600-212-1511') AND (([Sub Kit Comps_1].PER)=2))]. AS
[%$##@_Alias]
WHERE [Sub Kit Comps].KitItem IN (SELECT KitItem AS k2 FROM [Sub Kit Comps]
AS [Sub Kit Comps_2] GROUP BY KitItem HAVING COUNT (1) < 7);"

(note that Access added the "]. AS [%$##@_Alias]" foolishness, not I).

If I look at this string by hovering the cursor over it while execution is
paused, it shows 2 rectangular symbols before the 1st "FROM". Thinking that
the code was getting a CR-LF that it didn't like, I killed the EOL's in the
original query (from within the database window), but this did not prevent
the error.

Has anyone seen this before? Is it something to do with the length of the
string strSQL?

Thanks!

 

[Stefan Hoffmann]

hi Allen,

Here's a weird one. The code

Set qy = CurrentDb.QueryDefs(strSourceQuery)
strSQL = qy.SQL

Set qy = CurrentDb.QueryDefs("Kits Found Special")
qy.SQL = strSQL

produces error 3131: "Syntax error in FROM clause", despite the fact that
the 1st query works fine if run from the database window.
(note that Access added the "]. AS [%$##@_Alias]" foolishness, not I).

This alias is need by Jet to run the query. Access use this not so nice
default alias, which is not reusable as you have noticed.

The simple solution is to use a extra query for your subselect.

btw, under Access 2003 this probem is fixed.

mfG
--> stefan <--

 

[Guest]

Thanks, Gunny. That worked a treat.

I guess Access' [%$##@_Alias] thing is like the 'AS DerivedTable' syntax
recommended in T-SQL.

Hi, Allen.

Any advice?

When editing a Jet-induced pain, replace the opening bracket and closing
bracket of the subquery with opening and closing parentheses, then assign
your own alias name (avoiding reserved words and illegal identifiers). For
example, change:

SELECT KitItem
FROM [SELECT KitItem
FROM SubKits
WHERE SubKits.ITEM = '600-211-1231']. AS [%$##@_Alias]
WHERE (DateCreated >= #12/1/2006#)

. . . to:

SELECT KitItem
FROM (SELECT KitItem
FROM SubKits
WHERE SubKits.ITEM = '600-211-1231') AS Alternator
WHERE (DateCreated >= #12/1/2006#)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

I'm trying to modify my original query, and I'm being plagued by this
auto-generated non-reusable alias rubbish again.

Stefan said it was fixed in Access 2003; but, I am using 2003, and it ain't
fixed!

Any advice?

hi Allen,

Allen_N wrote:
Here's a weird one. The code

Set qy = CurrentDb.QueryDefs(strSourceQuery)
strSQL = qy.SQL

Set qy = CurrentDb.QueryDefs("Kits Found Special")
qy.SQL = strSQL

produces error 3131: "Syntax error in FROM clause", despite the fact that
the 1st query works fine if run from the database window.
(note that Access added the "]. AS [%$##@_Alias]" foolishness, not I).
This alias is need by Jet to run the query. Access use this not so nice
default alias, which is not reusable as you have noticed.

The simple solution is to use a extra query for your subselect.

btw, under Access 2003 this probem is fixed.

mfG
--> stefan <--

 

['69 Camaro]

Thanks, Gunny. That worked a treat.

You're welcome, Allen. Glad it helped.

I guess Access' [%$##@_Alias] thing is like the 'AS DerivedTable' syntax
recommended in T-SQL.

No one in his right mind would pick such a bizarre alias for the subquery, so
the default "[%$##@_Alias] thing" is pretty much guaranteed to be unique when
Jet slaps it on to optimize the query. And as you found out, once it's been
optimized like this, editing it (or even copying it or importing it to another
database) breaks it. Go figure.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

Thanks, Gunny. That worked a treat.

I guess Access' [%$##@_Alias] thing is like the 'AS DerivedTable' syntax
recommended in T-SQL.

Hi, Allen.

Any advice?

When editing a Jet-induced pain, replace the opening bracket and closing
bracket of the subquery with opening and closing parentheses, then assign
your own alias name (avoiding reserved words and illegal identifiers). For
example, change:

SELECT KitItem
FROM [SELECT KitItem
FROM SubKits
WHERE SubKits.ITEM = '600-211-1231']. AS [%$##@_Alias]
WHERE (DateCreated >= #12/1/2006#)

. . . to:

SELECT KitItem
FROM (SELECT KitItem
FROM SubKits
WHERE SubKits.ITEM = '600-211-1231') AS Alternator
WHERE (DateCreated >= #12/1/2006#)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

I'm trying to modify my original query, and I'm being plagued by this
auto-generated non-reusable alias rubbish again.

Stefan said it was fixed in Access 2003; but, I am using 2003, and it ain't
fixed!

Any advice?


:

hi Allen,

Allen_N wrote:
Here's a weird one. The code

Set qy = CurrentDb.QueryDefs(strSourceQuery)
strSQL = qy.SQL

Set qy = CurrentDb.QueryDefs("Kits Found Special")
qy.SQL = strSQL

produces error 3131: "Syntax error in FROM clause", despite the fact
that
the 1st query works fine if run from the database window.
(note that Access added the "]. AS [%$##@_Alias]" foolishness, not I).
This alias is need by Jet to run the query. Access use this not so nice
default alias, which is not reusable as you have noticed.

The simple solution is to use a extra query for your subselect.

btw, under Access 2003 this probem is fixed.

mfG
--> stefan <--