Duplicate output destination

[DoDev]

In Visual Basic I get a duplicate output destination error for the following
query

SELECT *
FROM tblB INNER JOIN (tblD INNER JOIN (tblC INNER JOIN tblA ON tblC.WID =
tblA.ZID) ON tblD.XID = tblC.XID) ON tblB.YID = tblA.YID
WHERE (((tblA.WID)=129) AND ((tblC.XID)=1))

Access is complaining about the XID field which is defined in two tables
(tblC.XID, tblD.XID)

Please notice the tables are inner joined through XID field so in the query
result in each row I have only one (unique) value of XID. Instead of using
SELECT *, I can solve the problem by individually listing in the SELECT
statement all the fields, including either tblC.XID or tblD.XID. However,
the statement would be very long, tedious to create and it would be painful
to maintain. In the future, each time I define a new field in any of the
tables I will have to remember to add the new field to the query.

By the way, I tested the query outside of Visual Basic. When I run exactly
the same query in Access Query Editor it works fine and produces desirable
results.
Any ideas on how I can solve the problem?
Thanks for your help.
DoDev

 

[Bob Barrows]

In Visual Basic I get a duplicate output destination error for the
following query

SELECT *
FROM tblB INNER JOIN (tblD INNER JOIN (tblC INNER JOIN tblA ON
tblC.WID = tblA.ZID) ON tblD.XID = tblC.XID) ON tblB.YID = tblA.YID
WHERE (((tblA.WID)=129) AND ((tblC.XID)=1))

Access is complaining about the XID field which is defined in two
tables (tblC.XID, tblD.XID)

Please notice the tables are inner joined through XID field so in the
query result in each row I have only one (unique) value of XID.
Instead of using SELECT *, I can solve the problem by individually
listing in the SELECT statement all the fields, including either
tblC.XID or tblD.XID. However, the statement would be very long,
tedious to create and it would be painful to maintain. In the
future, each time I define a new field in any of the tables I will
have to remember to add the new field to the query.

I know it's not what you want to hear, but that's the right way to do it.
Sorry.
It's considered good programming practice to explicitly list the fields you
wish to select in your queries. That way, your code does not break if you
add new fields that are unrelated to your code to any of your tables. Also,
the practice makes you think more about which fields you wish to retrieve
and streamline your select statements. For example, why in the world do you
want to waste resources and bandwidth retrieving the same value twice in
each record? Further, is it really necessary to retrieve all the other
records? It might be, but it's something you should be making a conscious
decision about rather than basing your decision on which is easier to type.
"Select *" should be reserved for single-use ad hoc queries.

You can reduce the typing by using table aliases. Instead of

Select somelongtablename.fieldname ... from somelongtablename
use an alias:
Select s.fieldname ... from somelongtablename as s

Bottom line: ADO (and all the other data access technologies I've used)
requires the fields in a recordset to have unique names.