MS Access - Keeping track (transacation) of all activity while form is open...

Question

Hi and thanks for reading. I am not a database/vba guy and my experience is strictly in the JAVA world. However, I need to have an ACCESS form open. While it is open and new data is entered into it (via the underlying query OR any other data changes in other tables based on sql updates attached to buttons on this form), is there a way that I can initiate a transaction so that at the bottom of the form I could have a button that says "Exit and committ changes" or "Cancel and Rollback changes".

I guess I thought of a situation where I could do something like:

1. On Form OPEN, START TRANSACTION

1. On "CLICK OK", COMMIT TRANSACTION or ON "CLICK CANCEL" ROLLBACK.

I'm aware how to do the Workspace.BeginTransaction but I'm not sure if that will apply if I do it on FORM OPEN and then on FORM CLOSE do the COMMIT/ROLLBACK locgin?

Any insight would be great.

Answer 1

Assuming you are using bound controls then the escape key cancels changes. Thus you need to customize your Ok button to do a close and save, and your cancel button to do escape and close.

Outside of that, the options aren't great, because you have to work with unbound controls (loading values yourself).

Answer 2

I am just thinking of two different methods to implement your idea:

1. one of them is to generate corresponding SQL INSERTS, UPDATES and DELETES instructions on the fly and send them to the server through a transaction. We have implemented such a solution, creating local/temporary tables, tracking changes on these tables, and then generating the corresponding SQL instructions to be launched against the database server. Putting on tracks such a solution is long process, and it does not make sense to think about it just for one form.
2. The other solution is to connect to your database through an ADODB connection, You can then manage disconnected recordsets and chose to proceed to batch updates when your data on the client side is ready to be uploaded, or abandon the changes. If I were you, I'll go for this one.