Updating related tables (parent / child) records using VBA

[GLT]

Hi I have a feeling that the answer to this question is going to be a bit
messy and complicated but here is the question:

I have two tables Activity Summary and Activity Detailed and both tables are
related via a primary / foreign key.

The Activity Summary table contains our remote servers and what time the
Backup Started / Completed and the Backup Status.

The Activity Detailed table contains further infomation for each backup that
is listed in the Activity Summary table.

My question is:

as the VBA code scans through our servers and records an entry in the
Activity Summary table, the VBA code performs additional loops that do
further analysis and adds that information to the Activity Detailed table -
this works except the parent / child records loose their relationship as
records get added to each of the tables.

When running VBA, how do i create an entry for the parent table, and then
enter related records for the child table and keep all records related?


All help is greatly appreciated...

Thanks,
GLT

 

[Tony Toews [MVP]]

Hi I have a feeling that the answer to this question is going to be a bit
messy and complicated but here is the question:

Nope. Thanks for the excellent description of your problem and
tables.

When running VBA, how do i create an entry for the parent table, and then
enter related records for the child table and keep all records related?

Once you've created the record for the parent table fetch the
autonumber ID of the just created record using the following code.
Then put that value in the foreign key of the child table.

If DAO use
RS.Move 0, RS.LastModified
lngTableListID = RS!stlid

If ADO use
cn.Execute "INSERT INTO TheTable.....", , adCmdText +
adExecuteNoRecords
Set rs = cn.Execute("SELECT @@Identity", , adCmdText)
Debug.Print rs.Fields(0).Value

cn being a valid ADO connection, @@Identity will return the last
Identity (Autonumber) inserted on this connection.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[GLT]

Hi Tony,

Thanks for your info and assistance - it works great.

Cheers,
GLT.