undo changes

[Bernie]

I'm looking for some hints/routines that allow me to undo/redo changes in
query linked to several tables.

Has anyone got any idea how to do this? Standard undo function is no good as
it only work in one table but not over several tables.

Working in access 2003.

Any comments/hints/ideas/routines much appreciated.

 

[Allen Browne]

If you are comfortable with VBA code, you can BeginTrans, make the changes,
and then either RollBack or CommitTrans.

For an example, see:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html

 

[Bernie]

Dear Allen

Thank you for your reply. I knew about your link and the
rollback/committrans. Does it also work over queries?

Yes, I'm fairly comfortable with VBA cod.

Have you put my full-text search form in your web site? ..haven't checked yet.

Rgds

 

[Allen Browne]

Dear Allen

Thank you for your reply. I knew about your link and the
rollback/committrans. Does it also work over queries?

You can use transactions with action queries fired by the Execute method
(not RunSQL.)

 

[Bernie]

Dear Allen

Got the rollback. Need to explain a bit further. I query a query, then link
search results to form. User can then edit query and either save changes or
discard changes (additions and edited fields). How can I go about this?
Temporary history table? Undo function only works per table and when changing
to a new record, old value is gone.

Any idea?

 

[Allen Browne]

In Access 2000 and later, it is possible to save the form as unbound, and
use the form's Open event to:
- open a transaction
- open a recordset
- assign it ot the Recordset property of the form
- CommitTrans or Rollback in the form's Unload event.

However, there are several things that make this less than practical:
- Doesn't work with a subform that uses LinkMasterFields/LinkChildFields
(due to the way Access loads the subform afresh each time the main form
record changes.)

- Concurrency issues in a multi-user environment (where users don't have the
latest copy of the data, and the multiple overlapping transactions are a
mess.)

- Less stable (crashes more often in my experience.)

 

[Bernie]

Dear Allen

Thank you for the reply. The limitations are no good and means I have to
record all changes in a temporary table and rollback manually. It can be done
yet cumbersome to programme. I was hoping Access would offer more than just
this. I'm sure SQL has rollback incorporated.

Although it's not that good news, I thank you for the help!

Rgds