Insert a record in one table then need the record ID for another?

[Aldred@office]

Hi,
I am doing a classic insert to multiple related tables in Access. What I
used to do is to insert one record in the top table, then select the just
inserted record with some data and fetch the ID out. The ID will be used
for inserting to other related tables.

Is the above method the only way in solving the problem? Is there any
newer/better way to do that? Like I could have the ID right away without
doing another Select?

Also, can I do one insert into 3 different related tables? What I used to
do in SQL was that I will use trigger. If anything goes wrong, the DB will
revert to what it used to be. But if I seperate that into 3 inserts, if
something wrong during the process, the data integrity might have problem.

Can someone suggest me some ideas on attacking the issue?

Thanks.

 

[Allen Browne]

You can get the primary key value from the last INSERT using a select query
as you say. Here's now it's done in JET:

Function ShowIdentity() As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = DBEngine(0)(0)
db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;"
Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

The alternative is to OpenRecordset(), AddNew with Update, set the Bookmark
to the LastModified, and you can then read the key value. This example uses
the Recordset of the form to duplicate the value in the form (using
Recorset) and the related records in a subform (using INSERT INTO):
http://allenbrowne.com/ser-57.html

Using either Execute or OpenRecordset, you can put the whole operation in a
transaction, for an all-or-nothing result (CommitTrans or Rollback.) Here's
an example of that:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html