DAO Transactions

[Don Stone]

I have a DAO database form in a split database. The form, through tabbing,
uses multiple linked tables. I believe that I need to use a transaction to
ensure that all related tables get updated as a unit if updates are all
successful or no updates if an error is encountered.

I have some reason to believe that transactions are not allowed for linked
tables. Is this true? And, if OK for linked tables, can someone point me
to some good help to code this problem?

Thank you in advance for any help provided.

Don

 

[David W. Fenton]

I have some reason to believe that transactions are not allowed
for linked tables.

In code, yes. In forms, no.

 

[david]

Broken, AFAIK, when used with SQL Server linked tables,
but still good with MDB linked tables.

Not useful with bound forms since they moved the bound
forms to a different connection, outside the default DAO
workspace.

You can bind the forms to local tables, and then use VBA
code to update the linked tables, using a transaction to
collect all the updates.

But that is only for data that is not-relational for a reason,
which is either because you have data logging and auditing,
which doesn't fit neatly into a relational model because you have
deliberately duplicated the data, and you deliberately want to
capture incoherent states, or because you have a many-many
data model, which is also rare.

If you aren't doing that, you should use Declarative Relational
Integrity to ensure that your tables are updated correctly,
and if you can't do that, you should re-consider your table
design. Perhaps it is time to move to a more complex table
design now.

(david)