By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,255 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

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

P: 3
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?
Expand|Select|Wrap|Line Numbers
  1. strSearch=lstNames.list(lstNames.listindex)
  2. SELECT * FROM t1, t2, t3, t4 WHERE t1.a >= " & strSearch
Feb 6 '11 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM ((t1 INNER JOIN t2 ON t1.a = t2.a) 
  3.           INNER JOIN t3 ON t2.a = t3.a) 
  4.           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.

Feb 6 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.