Double fieldnames in tables in a query

[Rob Berkers]

I use the next piece of program to open a query on two tables:


strSQL2006 = "SELECT Jaar2006.*, ARTICLE1.*" _
& "FROM Jaar2006 LEFT JOIN ARTICLE1 ON Jaar2006.ART_CODE =
ARTICLE1.ART_CODE"

Set rstArt1 = New ADODB.Recordset
rstArt1.Open strSQL2006, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic


This works without problems. The two joined tables (jaar2006 &
article1) are openend and I can access the records.

But in both tables there are fieldnames with the same names
("art_code" and "ag_code" for example).

When I use one of these "double" fields in the way as I normally do
("rstArt1![art_code]", "rstArt1![ag_code]" and so on) I get an error
telling me that the field could not be found.

When I use a field that is unique to both tables, there is no problem.

So I assume that I have to make clear in case of a double field from
which table the value should be taken.... but who do I do this?

(Sorry for my poor English)

Rob.

 

[John Spencer]

For those fields with duplicate names you should use. For those that are
not duplicated, DO NOT use the tablename.
rstArt!Fields("Jaar2006.Art_Code")
or
rstArt!Fields[Jaar2006.Art_Code]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..