Working with 2 or more tables from database (Access) 3

[Jdoggers]

Hi,
I have a program that i want to access two different tables in an Access database using ado connections. These tables in the database have a relationship. The first table (setuptab) has just 3 fields right now: setupID (primary key), setupname (name of the setup), and cellid (foreign key to second table). The second table (celltab) has just two fields: cellid (primary key), and cellname (name of the cell associated with the setup in the first table). Is there any way to take the cell id in the first table and bring up the cell name from the second table (celltab). Im putting the information into textboxes on a single form. The code im using for part of this looks like:

Dim adoconnect As Connection
Set adoconnect = New Connection


adoconnect.Open "PROVIDER=Microsoft.jet.OLEDB.4.0;" _
& "Data Source=C:\mydb.mdb;"

Set adoRecordset1 = New Recordset

adoRecordset1.Open "select * from setuptab", _
adoconnect, adOpenStatic, adLockOptimistic

'sname is the name of the textbox to put data into
Set sname.DataSource = adoRecordset1
sname.DataField = "setupname"

'this is where i need to figure out how to reference that second table and use it to populate the second textbox named "cname".


oh, by the way, in the first table (setuptab) the cell id field holds the matching id that is in the second table (celltab). Any suggestions?

thanks

 

[fredericofonseca]

You either have one SQL for each table or you use a single SQL for all tables. Depends on how your data is organized.

You will need to use either a JOIN or (and) a WHERE clause to determine which entries from the second table you need.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Jdoggers]

Hi,
I have some code here and i was wondering if anyone knows what the heck im doing wrong??

adoRecordset2.Open "select celltab.Cellname from celltab where setuptab.cellid = celltab.CellID", _
adoconnect, adOpenStatic, adLockOptimistic

in this piece of code, im trying to open a new recordset which ive already defined and set to a new recordset. With this code, i would like to join a two tables and take the "cellname" field from the "celltab" table where the "cellid" and the "cellid" fields in the two tables are equal. Basically the two tables are relational in the cellid field. The cellid field in the celltab table is the primary key, and the cellid field in the setuptab is just to tell which one of the cells is used in that setup.

thanks

 

[MarkSweetland]

join two tables and take the "cellname" field from the "celltab" table where the "cellid" and the "cellid" fields in the two tables are equal.

"select setuptab.setupname, celltab.cellname from setuptab, celltab where setuptab.cellid = celltab.cellid"







Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[gmmastros]

select celltab.Cellname from celltab Inner join StupTabl On celltab.CellID = setuptab.cellid

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Jdoggers]

Hey,
Marksweetland, that solution that you posted worked pretty good. The only thing is that instead of leaving the recordset the way it looks in the table, it sorted the data by name so that the setup name doesnt correspond with the actual cell associated with it...here is what i mean:

Table 1: "setuptab"...setupid is the primary key

fields: setupid setupname cellid
1 setup 1 2
2 setup 2 4
3 setup 3 3
4 setup 4 1
5 multi set 1

Table 2: "celltab"...cellid is the primary key

fields: cellid cellname
1 cell a
2 cell b
3 cell c
4 cell d
5 cell e


I have two text boxes that are tied to the new recordset and these output:

setup 1 cell a
setup 2 cell a
setup 3 cell b
setup 4 cell c
multi set cell d

and the actual output should be:

setup 1 cell b
setup 2 cell d
setup 3 cell c
setup 4 cell a
multi set cell a


any suggestions guys??

thanks

 

[MarkSweetland]

If you check the raw recordset you'll find the actual output is:

setupname cellname
---------- ----------
setup 1 cell b
setup 2 cell d
setup 3 cell c
setup 4 cell a
multi set cell a

I think it's your bound textboxes...





Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[Jdoggers]

marksweetland,

heres the code im using:

Dim adoconnect As Connection
Set adoconnect = New Connection


adoconnect.Open "PROVIDER=Microsoft.jet.OLEDB.4.0;" _
& "Data Source=C:\mydb.mdb;"

Set adoRecordset1 = New Recordset
Set adoRecordset2 = New Recordset

adoRecordset1.Open "select * from setuptab", _
adoconnect, adOpenStatic, adLockOptimistic

'sname is the name of a text box to display setup name
Set sname.DataSource = adoRecordset1
sname.DataField = "setupname"


adoRecordset2.Open "select setuptab.setupname, celltab.cellname from setuptab, celltab where setuptab.cellid = celltab.cellid", _
adoconnect, adOpenStatic, adLockOptimistic


'cname is the name of the other textbox to display cell name

Set cname.DataSource = adoRecordset2

cname.DataField = "Cellname"


the code that is used to move throughout the database is attached to two buttons on the screen "forward" and "back"

private sub forward_click()

If Not adoRecordset1.EOF Then
adoRecordset1.Movenext
End If
If Not adoRecordset2.EOF Then
adoRecordset2.Movenext
End If

end sub

private sub back_click()


If Not adoRecordset1.BOF Then
adoRecordset1.MovePrevious
End If
If Not adoRecordset2.BOF Then
adoRecordset2.MovePrevious
End If


end sub


i figured i would include the code so that you could take a look at it, i might be doing something wrong somewhere else

thanks

 

[MarkSweetland]

You only need one recordset since the relationship has been taken care of by the join in the SQL statement and both columns contain the "related" data:

adoRecordset1.Open "select setuptab.setupname,  celltab.cellname " & _ 
 " from setuptab, celltab " & _ 
 " where setuptab.cellid = celltab.cellid " & _ 
 " order by setupid", _
    adoconnect, adOpenStatic, adLockOptimistic

'sname is the name of a text box to display setup name
Set sname.DataSource = adoRecordset1
sname.DataField = "setupname"
Set cname.DataSource = adoRecordset1
cname.DataField = "cellname"

private sub forward_click()
     If Not adoRecordset1.EOF Then
         adoRecordset1.Movenext
     End If
     [s]If Not adoRecordset2.EOF Then
         adoRecordset2.Movenext
     End If[/s]
end sub

private sub back_click()
     If Not adoRecordset1.BOF Then
         adoRecordset1.MovePrevious
     End If
     [s]If Not adoRecordset2.BOF Then
         adoRecordset2.MovePrevious
     End If[/s]
end sub

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[Jdoggers]

hey man,
thanks, that worked perfectly. This whole database thing is like trying to learn chinese or something. If I wanted to add more text boxes and have those records associated with the setup...lets say i wanted to add some other catagories besides cellname, lets say like fruitname and drinkname or something, how would i modify the above code to tie those fields to other textboxes that do the same thing as cellname?

thanks