Should I use transactions in forms?

Topic

New Reply

In Access (97 in my case), is it common to design forms so that if any changes are made to the data, the user has an opportunity to Commmit or Rollback the transaction before moving to the next record? This seems like the most logical way to design forms since most Windows programs allow you to OK or Cancel. In our Informix 4GL apps at work we don’t commit any data until the user presses the commit key or we roll back if they press abort.

Thanks,
jim jones
If I bind the form to a table or query, it updates the data when I move to the next record. For lack of ideas, I tried a BeginTrans in the Form_Load event and then a prompt in the Form_BeforeUpdate event. However, a Rollback does not have any effect using this strategy.

Am I missing something? Do I have to load each Form element in VBA rather than binding the form and then take more of a VBA approach? It seems like this would be an option or something.

Reply | Quote

Replies

Yes, you are missing something. Access uses bound data by default, and moving to another record (or moving to a subform) triggers the afterupdate event of the form and writes the unsaved record. How you handle confirmations depends on how the application is designed, but transactions aren’t the answer to this problem.

What you can do is use the BeforeUpdate event of the form to allow the user to confirm that yes, they really do want to save this record. However, your users must be a lot more tolerant than mine. If I required users to confirm every record before saving, they’d lynch me in nothing flat! One alternative would be to supply an Undo Edits button that would either call the Undo method of the form or would write the old value of the fields into them again if they had used Shift-Enter to save the record. That, however, requires you to store the original values somewhere, often in the tag of each control, so that you can roll it back; and it won’t work if they’ve already moved to another record, since the tags will have been overwritten.

If you want to do a batch update to the table, you can’t do it using the default tools in Access. In Access 2000, using ADO, you can work this kind of magic using a persisted recordset. In 97, the only way I can think of offhand is to have your form based on an intermediate table that will accept changes and then if the user confirms all the changes will execute an update query to write them to the real table. However, if you create new records in the form, you will have serious key conflicts in a multiuser environment.

Reply | Quote

For me transactions are to allow one to make entries/changes to multiple tables/records and be certain that ALL have been performed successfully before the Commit otherwise one uses Rollback to put the records back to where they were at the start. For a single record you would use record locks.
To your question. One of my customers wanted this behaviour because the older, non-relational, database that was being replaced had always worked that way. Charlotte has got good advice in her reply. My question to you is how are you working with the records? If you have the records on a form displaying only one record at once then you may wish to consider how you get to the other records. Can you put two choices before the user can move to aother record? If your form is continuous then this wont work because of the mouse being able to change records.

Reply | Quote

I was unaware of the form Undo method.That answers a lot. I fell like a bit of a dolt now.

It’s been a little difficult to transition to the concept of bound forms with Access, but your answers clear it up quite a bit. I suppose Charlotte is right – users would have to be fairly patient to put up with confirmations for each update.

I’m accustomed to programming in a Informix RDB with an tool in which we don’t use bound forms. Users make modifications on the form and press accept or cancel keys. We don’t lock records until after the accept key is pressed and we really just use record locking for referential integrity.

Thanks for helping this Access newbie!
jim

Reply | Quote

There’s nothing that requires you to use bound forms in Access. I often use unbound forms for data entry, especially in Access 2000, since that’s the only way to use an ADO recordset with an Access form and have it updateable. However, if you use bound forms, Access does most of the work for you, so why not take advantage of that?

If you want something more like what you’re used to as far as the feel goes, just turn off the navigation and recordselector properties of the form and put a button on there for Commit (or whatever is comfortable). You’ll have to set the Cycle property to current record as well to keep the user from navigating off the current record by some other method.

Then, when they hit commit, use code to move to the next record. Disable the X button in the upper right hand corner and force them to use a Close button that you manufacture. In the code behind that, you can pop up a message box if the form is dirty (that is, if they’ve edited the current record without saving it) to ask if they want to save the record before exiting the form. Then you can undo or save as indicated before you close.

Reply | Quote

You guys are so cool. You and Andy are tons of help!

Reply | Quote

I’d like to look at implementing a transaction/work space that spans the entire application. This is with Access 97 using Access native tables; IE, no other database tables.

Once the user opens a specific form, the beginTrans is set.
I’ve declared a public variable in the modules section as
Public currentWS as workSpace

In the form_Open event I use the following code:

Set currentWS = DBEngine.Workspaces(0)
currentWS.BeginTrans

My plan is that when this form closes, I have a message box asking if the user wishes to save their changes. if yes, commit otherwise rollback.

The form in question opens several other forms which facilitates changing various tables.

Is this doable?
Thanks.

Reply | Quote

No. See http://support.microsoft.com/support/kb/ar…s/Q131/5/35.asp (Skip to the third paragraph of the “More Information” part).

Reply | Quote

I read the KB article you mentioned but it doesn’t indicate anything about declaring and using work spaces or transactions. If I declare the transaction and begintrans within a module, doesn’t that transaction session stay in scope until iether the form or database is closed?

Reply | Quote

No. You can only use transactions within a workspace (in Access 97). However, in spite of the global variable, if you set the workspace object variable within the form, its scope is limited to that form. There is no way to actually create a global workspace object. If you move to a subform on the same form, your transaction will not work the way you expect because another form now has the focus, so you’re out of scope. Transactions are NOT global in Access/Jet.

Reply | Quote

No. Simple, brutal answer but as far as I know correct.

If you want transaction support like that then either don’t use Access or use access but only with unbound forms (and that means no continuous forms).

Reply | Quote