Transaction across forms

[Lyn]

Hi,
I have two tables in a many-to-many relationship, linked by a third table.

TableA
RecNumA (Autonum)
[other fields...]

TableB
RecNumB (Autonum)
[other fields]

TableC (linking table
RecNumA (reference to primary key of TableA)
RecNumB (reference to primary key of TableB)

FormA is used to maintain records in TableA.
FormB is used to maintain records in TableB.

Whenever FormA is used to create or update a TableA record, FormB may
optionally be opened to make an associated change to TableB. TableC will
also be updated as necessary to link the TableA and TableB records.

Here (finally!) is my question. I would like to update all three tables
(when changed) in a Transaction. Can a Transaction (BeginTrans ->
CommitTrans) encompass table changes made on two different forms? Or do the
table updates all have to be made together in the one form? If the latter,
any suggestions on how to do this?

For example, is this feasible:

(FormA)
wrk.BeginTrans
(update TableA)
(open FormB and update TableB and TableC)
(close FormB)
wrk.CommitTrans

This would require looping in FormA waiting for FormB to close (not a good
idea). Could use the Close event of FormB somehow to trigger FormA to
commit?
Thanks for any help.

Lyn.

[Tom van Stiphout]

On Sun, 6 May 2007 17:05:29 +1000, Lyn <l.*******@iiNet.net.auwrote:

Transactions put severe restrictions on other use of the underlying
tables, so they should be run as quickly as possible. Therefore they
should never be controlled by user (in-)action.

TableA and TableB typically would have unrelated information, so it
generally is not true that a change in TableA would require an
associated change to TableB. If it does, that points to bad database
design.

-Tom.

>Hi,
I have two tables in a many-to-many relationship, linked by a third table.

TableA
RecNumA (Autonum)
[other fields...]

TableB
RecNumB (Autonum)
[other fields]

TableC (linking table
RecNumA (reference to primary key of TableA)
RecNumB (reference to primary key of TableB)

FormA is used to maintain records in TableA.
FormB is used to maintain records in TableB.

Whenever FormA is used to create or update a TableA record, FormB may
optionally be opened to make an associated change to TableB. TableC will
also be updated as necessary to link the TableA and TableB records.

Here (finally!) is my question. I would like to update all three tables
(when changed) in a Transaction. Can a Transaction (BeginTrans ->
CommitTrans) encompass table changes made on two different forms? Or do the
table updates all have to be made together in the one form? If the latter,
any suggestions on how to do this?

For example, is this feasible:

(FormA)
wrk.BeginTrans
(update TableA)
(open FormB and update TableB and TableC)
(close FormB)
wrk.CommitTrans

This would require looping in FormA waiting for FormB to close (not a good
idea). Could use the Close event of FormB somehow to trigger FormA to
commit?
Thanks for any help.

Lyn.