multiple tables, recordsets, and sql ?

[cyman73]

Hello all,

I am definitely a beginner with VB 6.0. I have a project where I need to open an access database and display the data from multiple tables. I don't have a problem displaying data from an individual table. I stumble when I need to combine info from two other tables to display the data from one table.

here is what I'm doing for multiple databases and recordsets where each recordset represents infividual tables in the database:

dim db, db2, db3 as database 'same database source
dim rs. ms, mm as recordset 'each recorset will represent a different table

Set db = OpenDatabase("C:\..\..\book.mdb")
Set rs = db.OpenRecordset("artist")
Set db2 = OpenDatabase("C:\..\..\book.mdb")
Set ms = db.OpenRecordset("cd")
Set db3 = OpenDatabase("C:\..\..\book.mdb")
Set mm = db.OpenRecordset("made")

I have to display the fname and lname which comes from the same "artist" table. I will rs.movenext until rs.EOF
to iterate through the "artist"

txtArtistFirstName = rs.fields("fname")
txtArtistLastName = rs.fields("lname")

All other fields are available with their respective recordset. BUT I also have to display the artist's CD name and genre. Those fields,
CDname, genre are easily found in the "cd" table. But in order to know
the name of title and genre of the cd attributed to the current artist
I have to use the "made" table where CDID is matched with AID

Now, here is the select statement I need to use to assign the value
to the txtCDName. I know that the syntax is wrong but what do I use
intead, so that is where I need help.

txtCDTitle = "select cd.cdTitle
from "cd"
where cd.CDID = (select made.CDID
from "made", "artist"
where made.aid = artist.aid)

The only thing I know is that the recordset made from the artist
table points to the artist.aid that I am using in the sql statement
artist.aid = rs.Fields("aid")

I hope this post is not confusing.

Any help is greatly appreciated. Thank you in advance.

[Phil Weber]

First of all, you do not need to open the same database three times in order to create three recordsets. In fact, I just looked at your code again, and it looks like you're not even using db2 and db3, so you can delete those lines.

Now, if I understand your database structure correctly, the query you want is:

SELECT cd.cdTitle
FROM cd
INNER JOIN made ON cd.CDID = made.CDID
WHERE made.aid = artist.aid

(Note that there are no quotes around the table names.) You may not, however, simply assign this SQL statement to a textbox; you must use the SQL to create a recordset, then assign the value of the recordset's cdTitle column to the textbox.

Also, artist.aid is a variable name in your code, correct? The SQL query processor doesn't know anything about your variables, so rather than passing the variable name in the SQL statement, you need to pass its value.

The code should look something like this:

SQL = "SELECT cd.cdTitle FROM cd " & _
"INNER JOIN made ON cd.CDID = made.CDID " & _
"WHERE made.aid = " & artist.aid

Set rsTitle = db.OpenRecordset(SQL)
txtCDTitle.Text = rsTitle.Fields("cdTitle")

[cyman73]

Thank you Phil.

Shortly after I posted my question I went to the library where I found a VB6 with databases for dummies. That book gave me the same logic. I wish I would have posted a question yesterday before I bought $80 worth of worthless books (at least with this subject).

I implemented your version and it works great. I only need to know if and how I update the recordset after inserting a new record in the database.

Thank you again.