DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: multiple tables, recordsets, and sql ?

  1. #1
    Join Date
    Nov 2005

    multiple tables, recordsets, and sql ?

    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.

  2. #2
    Join Date
    Nov 2003
    Portland, OR
    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")
    Phil Weber

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Nov 2005
    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.

Similar Threads

  1. Compare two sql server tables
    By Rinze in forum VB Classic
    Replies: 2
    Last Post: 02-08-2006, 11:44 AM
  2. treeview without using ActiveX
    By Aaron Coombs in forum ASP.NET
    Replies: 2
    Last Post: 09-26-2001, 03:29 AM
  3. Replies: 1
    Last Post: 04-17-2001, 09:59 AM
  4. SQL query progress bar ADO recordsets
    By Scott Downey in forum VB Classic
    Replies: 0
    Last Post: 01-29-2001, 11:11 AM
  5. Replies: 3
    Last Post: 10-19-2000, 08:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center