Form operations and transactions

[spikepl]

I want to do two operations in a form: add a record and update the preceding record. Either both operations or none.

I can find workarounds but would prefarably like to do it like this (normalization rules can go and ... themselves in this instance)

Wrapping bound form operations in a transaction is e.g. described by Microsoft here:

http://support.microsoft.com/kb/248011

and disparaged by reasonably known MVP's here:

http://stackoverflow.com/questions/1117155/can-i-wrap-an-access-form-with-a-transaction

What say you?

 

[Galaxiom]

Tony suggested temporary tables so he was not criticising the technique at all.

David is usually very knowledgeable but in this case he doesn't appear to be aware of the technique and is dismissing it out of hand.

Give it a try and please let us know how it goes.

 

[Galaxiom]

Just tried it. What a fabulously useful technique. Nobody should ever use temp tables for this again.

However I just had a glitch with adding a new records and updating an old one.

Will report back if I can do it again.

Must have done somethig dumb because it seems to work flawlessly now.

 

[jdraw]

spikepl,

I haven't used Transactions with Access. I did a few samples years ago.

I spent several years in corporate database(ADABAS) where we designed logical units of work and used transaction logic throughout. It was fantastic.

I'd definitely use it in your set up. Try it, let us know how it goes.

 

[ChrisO]

I like the concept but it seems a little too easy to break.

These days, anytime I see code in the Form_Unload or Form_Close events I get a bit suspect. The failures caused can be very subtle.

The problem in this case is that if a Quit command is issued, anywhere in the database, then boolFrmSaved is reset to 0 (False) before the Form_Unload event is called. That is probably fail safe but may not be what is required.

What that would mean is that any variables used after a Quit command would have to be stored in a more robust environment. Maybe a database property or TempVar (which I know nothing about) or table.

Demo of the failure is attached.

Chris.

 

[jdraw]

Here's another example that may be helpful.

 

[spikepl]

Thanks folks - most interesting.

Fortunately I have no "waiting period" in my two operations that take place consecutively and instantaneously, and so should be shielded from Chris O's quite clever failure mode

 

[MarkK]

It also seems a limitation that that transaction is now active across the whole DBEngine object. The form should then be modal so users don't leave the form, edit other data, and end up rolling back that other work.

Or, I guess you could open a database and recordset in a new workspace, and BeginTrans in just that workspace.