PDA

Click to See Complete Forum and Search --> : Inserting data into multiple tables

Vaderman
November 19th, 2004, 04:08 AM
I have setup a database in MS Access 2K that has several tables. There are several relationships that exists between tables. My question is this: If I insert a row of data in one table that stores foreign keys from other tables, how can I ensure that all tables concerned are updated using INSERT?

For example, I have the main table :

PK - Quote_id
FK - Customer_ID
Project_Name

and the Customer table :

PK - Customer_ID
Customer_Name


this is a 1:M relationship.

Using a C# Form, I enter the data for Customer Name and the Project Name. Using the INSERT, how can I populate, not only the main table, but the Customer table too?

At the moment I'm having to get the customer name and inserting that data into the Customer table then once the data has been inserted I get the new Customer_ID, using a SELECT. once I have the new Customer_ID, I then use that as part of my INSERT statement to populate the main table.

Surely there must be a more effecient way to updating multiple tables that share a relationship?

Regards

John
Krzemo
November 19th, 2004, 05:00 AM
U can use View for insertion.


Best regards,
Krzemo.
Vaderman
November 19th, 2004, 05:04 AM
Thanx for the reply Krzemo. However, your answer is correct if I was using Access only, but I'm using the Access 2K database with a .Net interface, and Access does not allow for stored procedures, otherwise I could've done what u have suggested.

Any other suggestions would be grateful.

Regards

John
Krzemo
November 19th, 2004, 05:14 AM
I'm telling U only to use VIEW (not a Stored Procedure).

Open "SELECT ... FROM tb1 INNER JOIN tb2 ..." etc
And it should work (but I'm not using Access with .NET - only VB, C++, VBA)

Best regards,
Krzemo.
erickwidya
November 21st, 2004, 08:51 PM
..how can I ensure that all tables concerned are updated using INSERT? try using cascade Update Related Field when u build relations at Access..
panayotisk
November 22nd, 2004, 03:09 AM
I think you need to separate INSERT statements anyway. There is no multitable insert. See the answer given to me in this thread (http://www.codeguru.com/forum/showthread.php?t=241304&highlight=INSERT)
erickwidya
November 22nd, 2004, 03:12 AM
..Using the INSERT, how can I populate, not only the main table, but the Customer table too? u must use SELECT to populate the record u want..
not INSERT and u can use what Krzemo suggestion..INNER JOIN