alias

[JB]

I am using Access 2003 (11.6355.6360) sp1 and am having trouble with the
query designer. Often if I create or modify a query in the SQL window, if I
open the query in the designer, it addes spurious aliases to anything it can
find to attach them to. This also happens if I import the queries into a
blank database. This it a major problem because I then must go though all
the queries one by one ( and in some cases there can be hundreds) in SQL view
and delete all the spurious aliases. Is there anyway to turn off this
"feature" ?

 

[Rick Brandt]

I am using Access 2003 (11.6355.6360) sp1 and am having trouble with
the query designer. Often if I create or modify a query in the SQL
window, if I open the query in the designer, it addes spurious
aliases to anything it can find to attach them to. This also happens
if I import the queries into a blank database. This it a major
problem because I then must go though all the queries one by one (
and in some cases there can be hundreds) in SQL view and delete all
the spurious aliases. Is there anyway to turn off this "feature" ?

No.

They are harmless are they not?

 

[JB]

Not true in some cases. They are not harmless

Eg. with code such as ..

Dim qry ...
set qry ..
set prm....
prm =

set rst = qry.RecordSet

with rst
lDataID = ![DataID]
end with
....


This depends on the rst field name being "DataID". When Access sticks in
the alias, the sql looks like "Select DataID as Expr1 from ... "
and the name returned by the rst is Expr1 and not DataID


John

 

[Rick Brandt]

Not true in some cases. They are not harmless

Eg. with code such as ..

Dim qry ...
set qry ..
set prm....
prm =

set rst = qry.RecordSet

with rst
lDataID = ![DataID]
end with
...


This depends on the rst field name being "DataID". When Access
sticks in the alias, the sql looks like "Select DataID as Expr1 from
... "
and the name returned by the rst is Expr1 and not DataID

I have never seen Access insert an alias for a field name, only for an
expression based on a field name.

 

[John Spencer (MVP)]

Well , it can do this if you have the same field name in the query select list
twice, but other than that I agree with you.

As far as JB's problem goes. Perhaps he could post a sample of what is
happening. That is the SQL as he has it written and then the SQL as it is
rewritten.

Not true in some cases. They are not harmless

Eg. with code such as ..

Dim qry ...
set qry ..
set prm....
prm =

set rst = qry.RecordSet

with rst
lDataID = ![DataID]
end with
...


This depends on the rst field name being "DataID". When Access
sticks in the alias, the sql looks like "Select DataID as Expr1 from
... "
and the name returned by the rst is Expr1 and not DataID

I have never seen Access insert an alias for a field name, only for an
expression based on a field name.

 

[JB]

Here is just an example. I create most of my queries using the SQL view
because that is more natural for me, expecially action queries.

I type in..
INSERT INTO tblCfg_DataDBList ( DataDbID, IsMultiClientDB, DataDBName,
DataDBExt, DataDBPath )
SELECT [prmDataDbID], [prmIsMultiClientDB], [prmDataDBName], [prmDataDBExt],
[prmDataDBPath]
WITH OWNERACCESS OPTION;

(actually. in this case, originally I typed in INSERT INTO ..() VALUES ( )
but DAO seems to prefer the SELECT format since it converted it when I went
to Query Design. But starting with the query above, I get the query below )

Click on Query Design
then
Click on SQL View
and get this,

INSERT INTO tblCfg_DataDBList ( DataDbID, IsMultiClientDB, DataDBName,
DataDBExt, DataDBPath )
SELECT [prmDataDbID] AS Expr1, [prmIsMultiClientDB] AS Expr2,
[prmDataDBName] AS Expr3, [prmDataDBExt] AS Expr4, [prmDataDBPath] AS Expr5
WITH OWNERACCESS OPTION;

The same thing happens if I create a blank database and import the queries.
It means lots of work to go through them and remove all the aliases.


--
JB

Well , it can do this if you have the same field name in the query select list
twice, but other than that I agree with you.

As far as JB's problem goes. Perhaps he could post a sample of what is
happening. That is the SQL as he has it written and then the SQL as it is
rewritten.

Not true in some cases. They are not harmless

Eg. with code such as ..

Dim qry ...
set qry ..
set prm....
prm =

set rst = qry.RecordSet

with rst
lDataID = ![DataID]
end with
...


This depends on the rst field name being "DataID". When Access
sticks in the alias, the sql looks like "Select DataID as Expr1 from
... "
and the name returned by the rst is Expr1 and not DataID

I have never seen Access insert an alias for a field name, only for an
expression based on a field name.

 

[JB]

I forgot to mention before but this "feature" is new to Access 2003. I never
had this problem when using Access 2000


--
JB

Well , it can do this if you have the same field name in the query select list
twice, but other than that I agree with you.

As far as JB's problem goes. Perhaps he could post a sample of what is
happening. That is the SQL as he has it written and then the SQL as it is
rewritten.

Not true in some cases. They are not harmless

Eg. with code such as ..

Dim qry ...
set qry ..
set prm....
prm =

set rst = qry.RecordSet

with rst
lDataID = ![DataID]
end with
...


This depends on the rst field name being "DataID". When Access
sticks in the alias, the sql looks like "Select DataID as Expr1 from
... "
and the name returned by the rst is Expr1 and not DataID

I have never seen Access insert an alias for a field name, only for an
expression based on a field name.

 

[John Spencer (MVP)]

Well, I've been avoiding the use of 2003 when I can. I just don't like the way
it implemented macro security and most of the new features were not significant
to the apps I have worked on.

The only thing I can suggest is to never switch to into query design. OR do
everything from VBA so you can build the query string there. I am assuming that
PRM stands for parameter.

Wish I could be more help than that, but I must admit I am stuck.

Here is just an example. I create most of my queries using the SQL view
because that is more natural for me, expecially action queries.

I type in..
INSERT INTO tblCfg_DataDBList ( DataDbID, IsMultiClientDB, DataDBName,
DataDBExt, DataDBPath )
SELECT [prmDataDbID], [prmIsMultiClientDB], [prmDataDBName], [prmDataDBExt],
[prmDataDBPath]
WITH OWNERACCESS OPTION;

(actually. in this case, originally I typed in INSERT INTO ..() VALUES ( )
but DAO seems to prefer the SELECT format since it converted it when I went
to Query Design. But starting with the query above, I get the query below )

Click on Query Design
then
Click on SQL View
and get this,

INSERT INTO tblCfg_DataDBList ( DataDbID, IsMultiClientDB, DataDBName,
DataDBExt, DataDBPath )
SELECT [prmDataDbID] AS Expr1, [prmIsMultiClientDB] AS Expr2,
[prmDataDBName] AS Expr3, [prmDataDBExt] AS Expr4, [prmDataDBPath] AS Expr5
WITH OWNERACCESS OPTION;

The same thing happens if I create a blank database and import the queries.
It means lots of work to go through them and remove all the aliases.

--
JB

Well , it can do this if you have the same field name in the query select list
twice, but other than that I agree with you.

As far as JB's problem goes. Perhaps he could post a sample of what is
happening. That is the SQL as he has it written and then the SQL as it is
rewritten.

JB wrote:
Not true in some cases. They are not harmless

Eg. with code such as ..

Dim qry ...
set qry ..
set prm....
prm =

set rst = qry.RecordSet

with rst
lDataID = ![DataID]
end with
...


This depends on the rst field name being "DataID". When Access
sticks in the alias, the sql looks like "Select DataID as Expr1 from
... "
and the name returned by the rst is Expr1 and not DataID

I have never seen Access insert an alias for a field name, only for an
expression based on a field name.

 

[JB]

Thanks for your time. I can easily avoid switching to QueryDesign view but a
bigger problem is that that same thing occurs if I create a blank db and
import everything into it which I do once in a while for various reasons.
The reason I am using stored queries at all is because I need user level
security and RWOP queries for this db. Normally I do code SQL strings in VBA
which I much prefer, particularly since the code is much the same whether the
back end is Access or SQL Server.

In this case, I guess I will try putting in aliases of my own and then code
for them instead of the actual names and see if Access will leave them alone.


--
JB

Well, I've been avoiding the use of 2003 when I can. I just don't like the way
it implemented macro security and most of the new features were not significant
to the apps I have worked on.

The only thing I can suggest is to never switch to into query design. OR do
everything from VBA so you can build the query string there. I am assuming that
PRM stands for parameter.

Wish I could be more help than that, but I must admit I am stuck.

Here is just an example. I create most of my queries using the SQL view
because that is more natural for me, expecially action queries.

I type in..
INSERT INTO tblCfg_DataDBList ( DataDbID, IsMultiClientDB, DataDBName,
DataDBExt, DataDBPath )
SELECT [prmDataDbID], [prmIsMultiClientDB], [prmDataDBName], [prmDataDBExt],
[prmDataDBPath]
WITH OWNERACCESS OPTION;

(actually. in this case, originally I typed in INSERT INTO ..() VALUES ( )
but DAO seems to prefer the SELECT format since it converted it when I went
to Query Design. But starting with the query above, I get the query below )

Click on Query Design
then
Click on SQL View
and get this,

INSERT INTO tblCfg_DataDBList ( DataDbID, IsMultiClientDB, DataDBName,
DataDBExt, DataDBPath )
SELECT [prmDataDbID] AS Expr1, [prmIsMultiClientDB] AS Expr2,
[prmDataDBName] AS Expr3, [prmDataDBExt] AS Expr4, [prmDataDBPath] AS Expr5
WITH OWNERACCESS OPTION;

The same thing happens if I create a blank database and import the queries.
It means lots of work to go through them and remove all the aliases.

--
JB

Well , it can do this if you have the same field name in the query select list
twice, but other than that I agree with you.

As far as JB's problem goes. Perhaps he could post a sample of what is
happening. That is the SQL as he has it written and then the SQL as it is
rewritten.

Rick Brandt wrote:

JB wrote:
Not true in some cases. They are not harmless

Eg. with code such as ..

Dim qry ...
set qry ..
set prm....
prm =

set rst = qry.RecordSet

with rst
lDataID = ![DataID]
end with
...


This depends on the rst field name being "DataID". When Access
sticks in the alias, the sql looks like "Select DataID as Expr1 from
... "
and the name returned by the rst is Expr1 and not DataID

I have never seen Access insert an alias for a field name, only for an
expression based on a field name.