Member Avatar for plusplus

I have the following select statement which works, I need help to adapt it

adobau.RecordSource = "SELECT * FROM Liegenschaften INNER JOIN tblbau ON Liegenschaften.Rimo_Liegnr = tblbau.Liegenschaft"

my adobau.recordset loops through Liegenschaften and gives me all the info of Liegenschaften that has match in tblbau, but I want some info from tblbau also, how would I do this?

  • 4 Contributors
  • 8 Replies
  • 123 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by plusplus

Dani AI

Generated

Brief recap: the issue was duplicate column names coming back from a JOIN, which made the ADO recordset return ambiguous field names. pointed out you must qualify columns in the SELECT, clarified that the recordset exposes the query columns (not tables), and confirmed that addressing the qualified/unique field name fixed the error.

A robust, non-invasive fix (so you don't have to rename existing DB columns) is to explicitly list the columns you need and give each a unique alias in the query. That guarantees the Recordset.Fields collection contains predictable names you can bind to or read from code.

Example pattern (SQL and VB/ADO access):

-- SQL: use table aliases and AS to create unique column names
SELECT t1.name AS OwnerName, t1.address AS OwnerAddr, t2.date AS BuildDate
FROM Liegenschaften AS t1
INNER JOIN tblbau AS t2 ON t1.Rimo_Liegnr = t2.Liegenschaft
' VB: read fields by the alias you created
Dim rs As ADODB.Recordset
Set rs = adobau.Recordset

TextBoxName.Text = rs.Fields("OwnerName").Value
TextBoxAddress.Text = rs!OwnerAddr
TextBoxDate.Text = rs.Fields("BuildDate").Value

Quick troubleshooting tips:

  • Avoid SELECT * with joins — it invites duplicate names.
  • If something still looks wrong, dump the returned field names to see what the driver actually produced:
For i = 0 To rs.Fields.Count - 1
  Debug.Print i, rs.Fields(i).Name
Next i

Some ODBC/ODBC-like drivers will present fields as table.field when names collide; aliases avoid that and make bindings stable for the many programs that depend on this DB. For reference, see SQL column aliasing and the ADO Fields collection documentation: and ADO Fields collection.

Recommended Answers

All 8 Replies

Member Avatar for debasisdas

there is no problem with the query . You might need to change the join condition . Kindly post both of your tables structure for more details.

Member Avatar for plusplus

tbl 1 has fields id, name,address
tbl2 has fields id, date
refers to same id of tbl1
I want all records from tbl2 print the name address and date.
name and address are in tbl1 and date in tbl2
Can this be done in one select statement?

Member Avatar for yello

Try this query.

adobau.RecordSource = "SELECT ,Liegenschaften.address,tblbau.date FROM Liegenschaften INNER JOIN tblbau ON Liegenschaften.Rimo_Liegnr = tblbau.Liegenschaft"

Member Avatar for plusplus

My problem is not the recordsource, it gives me all the records I need.
My problem is the recordset, it points only to one table and I need info from secondtable also

Member Avatar for culebrin

I think you should give a try the yello's suggestion, and to call certain column use: adobau("columnname"), columnname could be any of the columns used in the query, not exclusively of a certain table, the recordset does not know anything about the tables used in your query, it only can see the columns used in the query, all of them:

p.e:
adobau.RecordSource = "SELECT ,Liegenschaften.address,tblbau.date FROM Liegenschaften INNER JOIN tblbau ON Liegenschaften.Rimo_Liegnr = tblbau.Liegenschaft"

and the call:

textbox1.text = adobau("name")
textbox2.text = adobau("address")
textbox3.text = adobau("date")

and so on no matter which table you used in the query. That's why you should name with different names, similar columns in both tables.

Cheers,

Omar

Member Avatar for plusplus

ok, I guess that was my problem, I have in both tables a column with same name and when I try to get that one it gives me an error. I thought it's because the recordset doesn't point to that table, now I see that it's because it doesn't know which column, which table.
I can't change the names of the columns, because I'm using an existing database that's being used for hundreds of programs already. I got it to work with two ados and two select statement, but I'd still like to do it in one. Do you have any suggestions?

Member Avatar for yello

If you have same column name in two tables you must refer the column by [Table Name].[Column Name]

Member Avatar for plusplus

It works thank you all for your help
this was the syntax that worked
ado1.recordset ("tblname.columnname")

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.