aliases II, a warning

[Guest]

This is a continuation of the discussion of a 7/18. The problem is that
Access 2003 at times introduces spurious aliases for field names in queries.
This problem came up again when I rebuilt an .mdb, starting with a blank .mdb
and then imported queries, forms, etc, from the original .mdb.

When I did this, it turns out that the order things are done is important.
Starting with a query such as..

SELECT tblCfg_Version.Version
FROM tblCfg_Version
WHERE (((tblCfg_Version.TableID)=1))
WITH OWNERACCESS OPTION;

Where tblCfg_Version is a linked table, if the query is imported first
(before the table is linked), I get …

SELECT tblCfg_Version.Version AS Expr1
FROM tblCfg_Version
WHERE (((tblCfg_Version.TableID)=1))
WITH OWNERACCESS OPTION;

This, of course, breaks the code.

Howerer tf the table is linked before the query is imported, I get

SELECT tblCfg_Version.Version
FROM tblCfg_Version
WHERE (((tblCfg_Version.TableID)=1))
WITH OWNERACCESS OPTION;

which the correct result.

This seeems to be a "feature" new to Acess 2003. In Access 2000, the order
doesn’t matter and the spurious alias is not introduced.

 

[Marshall Barton]

This is a continuation of the discussion of a 7/18. The problem is that
Access 2003 at times introduces spurious aliases for field names in queries.
This problem came up again when I rebuilt an .mdb, starting with a blank .mdb
and then imported queries, forms, etc, from the original .mdb.

When I did this, it turns out that the order things are done is important.
Starting with a query such as..

SELECT tblCfg_Version.Version
FROM tblCfg_Version
WHERE (((tblCfg_Version.TableID)=1))
WITH OWNERACCESS OPTION;

Where tblCfg_Version is a linked table, if the query is imported first
(before the table is linked), I get …

SELECT tblCfg_Version.Version AS Expr1
FROM tblCfg_Version
WHERE (((tblCfg_Version.TableID)=1))
WITH OWNERACCESS OPTION;

This, of course, breaks the code.

Howerer tf the table is linked before the query is imported, I get

SELECT tblCfg_Version.Version
FROM tblCfg_Version
WHERE (((tblCfg_Version.TableID)=1))
WITH OWNERACCESS OPTION;

which the correct result.

This seeems to be a "feature" new to Acess 2003. In Access 2000, the order
doesn’t matter and the spurious alias is not introduced.

Ah, that's interesting. Seems to be an error recovery
"feature". If the field doesn't exist (and it can't if the
table doesn't exist either)m then it must be a calculated
field???

Thanks for posting this, it should prevent some confusion in
the future.

 

[Ken Snell [MVP]]

I think I've seen this behavior in ACCESS 2002 as well... when I was copying
SQL text for a query from one database to another, and one of the source
queries in that SQL hadn't been created first (user error -- me user!).