By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,461 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Transaction across forms

P: n/a
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.
May 6 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.
May 8 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.