ADO: Adding records to multiple related tables

Hi there!

My apologies if this question is one I should have found an answer to on the web or through any type of FAQ: I did spend quite some hours trying to figure this one but failed... :o

I'm working on what admittingly is my very first VB 6.0/Access 2000 project. In Access I've set up a number of tables, including (1:1;1:n;n:m) relationships through Autonumber-based ID fields.

I'm now at the point where I would like to add new records to these tables using ADO. And this is where I get puzzled... How do I achieve this from a general point of view? I know about AddNew, but none of the examples I found will demonstrate targeting multiple tables...

Personally what I think I need to do is:


Add a record to the main table using AddNew
Retrieve the (Autonumber) Primary Key using a seperate query
Add a record to one of the depending tables, using the Primary Key value retrieved in the previous step as the Foreign Key...


Could someone confirm whether this is indeed the approach to take? Is there maybe a SQL-based alternative that's more efficient or preferable for any other reason?

As you may guess, any suggestion is more than welcome!

Thank you in advance!
Cheers, Edgard

Personally what I think I need to do is:
Add a record to the main table using AddNew
Retrieve the (Autonumber) Primary Key using a seperate query
Add a record to one of the depending tables, using the Primary Key value retrieved in the previous step as the Foreign Key...


I encourage you to use the ADODB Connection Object in doing DML commands (INSERT,DELETE and UPDATE)

Autonumber is great but I suggest you maintain a sequence table. This is what we do in Oracle databases. You can set bach your sequence value as you wish.

A relational database wont allow you to add records if one business rule is violated. You third statement is correct. :)

I have recently added records to multiple tables using ADO objects try

db.Execute ("INSERT INTO table1(opcode, Event, Date_created, Date_of_issue, Review_date, written_by, Authorised_by)

opcode, Event, Date_created, Date_of_issue, Review_date, written_by, Authorised_by all being the feilds in the table and

VALUES('" & intNewHighest & "','','','','','','')")

Being the values

Make sure that you have the same number of fields in the first INSERT section and the last VALUES section i have been silly and this has given me annoying errors

You have to make sure that the Primary Key table has an entry first for any record you will insert where this same key is a Foreign Key.

Ex. You cannot create an Invoice for a Customer if the Customer is not yet in the Customers table.

Note: in Oracle, it's not a sequence table, it's just a sequence. A sequence is just a process in Oracle that generates a number (or group of numbers) that are guaranteed to be unique within the sequence. Since sequences are separate entities, and are independent of specific tables, you can use the same sequence generator for primary keys for multiple tables.

In database apps (especially for apps interfacing to Access tables, which because it's a file based system, it presents special issues) a somewhat different approach (although it takes a bit more storage space) for primary keys would be to generate a GUID in your app, and use it for your primary key. This is especially effective if you have multiple databases that you may need to merge or sychronize.

Thanks all for the kind advice! Will give it a go now...

Cheers, Edgard

Note: in Oracle, it's not a sequence table, it's just a sequence.
Yeah loquin, you are right. The sequence table I mentioned in my last post is not the Sequence function that Oracle have. Oracle's Sequence function provides unique values, applicable for primary keys. But they are not in random. The sequence table I meant is some what like the CG_Code_Controls of Oracle. Where you can set the actual number of the NextVal. Using CG_Code_Controls gives you the capability to generate unique values in a sequencial manner. :)

Here a link to a thread I started about sequences.
Click Me... (http://www.visualbasicforum.com/showthread.php?t=188288)