How to connect to access database with multiple tables and add/update the records?

[engrlorie]

I have four tables in access 2003: T1 (8 fields), t2 (5 fields), t3 (7 fields), t4 (3 fields) all connected to each other with t1.a = t2.a, t1.a=t3.a, t1.a=t4.a (T2, T3 and T4 contains the FK for T1.
My program has a listbox with t1.a populated on it. When the user clicks on an "a value," it would search the database and populate the textboxes which are supposedly connected to the fields (distributed to the 4 tables).
My concern is: is it possible to connect to all of these tables and populate my vb6 text boxes from them for viewing of records? I also need to be able to update the records.
Would this query work?

strSearch=lstNames.list(lstNames.listindex)
SELECT * FROM t1, t2, t3, t4 WHERE t1.a >= " & strSearch

[Stewart Ross]

Not as you show it, as there are no joins between the tables - you would have the Cartesian product of the four tables instead.

You'll need to use appropriate inner joins between the four tables, like this:

SELECT *
FROM ((t1 INNER JOIN t2 ON t1.a = t2.a) 
          INNER JOIN t3 ON t2.a = t3.a) 
          INNER JOIN t4 ON t3.a = t4.a;

You should really select the actual fields you need and apply appropriate aliases to guarantee uniqueness, however, as including them all will result in a number of name clashes that can only be resolved by qualifying the field names with the table name concerned.

you will also have to ensure if you want to do updates that you maintain the correct primary key/foreign key relationship throughout - no accidental changing of foreign keys that could leave records dangling without matching primary keys, for instance.

-Stewart