In an Access app, is there the concept of a transaction, with "start transaction", "commit", and "rollback" If so, how do I implement it in VBA
My app will have a main form withfour subforms on it. It will also have a Save button and a Cancel button. I would like to start the transaction when the main form is loaded, and commit only when the user clicks the Save button. The Cancel button would trigger a rollback. Changes made on the subforms would be temporary until the entire transaction is committed.
Can someone point me in the right direction for this, or better yet, show me an example
Thanks.
transaction processing in an Access app?
nahn
Hey man,
Personally I don't use bound forms. I like to control everything through code, displaying and saving. That way I can apply some rules, like transactions maybe, or data formatting, myself. It's just a personal preference. Think if your using bound controls then just stick with them, changing from bound controls to unbound controls would take a bit of time.
Thought that would have worked. Did another search to see if anything else came up and found this...
http://support.microsoft.com/ kbid=248011
Seems the approach to take with bound controls is to call DBEngine.BeginTrans instead of going through the connection object. Unfortunately the approach doesn't cover subforms.
lucho16209
I just tried the following:
In the On Open event for the form added
CurrentProject.Connection.BeginTrans
In the On Click event for the OK button added
CurrentProject.Connection.CommitTrans
In the On Click event for the Cancel button added
CurrentProject.Connection.RollbackTrans
Got the following error on both the OK and Cancel buttons:
Error #-2147168242 You tried to commit or rollback a transaction without first beginning a transaction.
I traced the On Load code, and the CommitTrans statement does get executed, so I'm not sure what's going on. Any ideas
Thanks for your help.
Marcel Haans
Hey man, I think your out of luck with the subform transactions....
http://support.microsoft.com/default.aspx scid=kb;en-us;208833
That article suggests you can't treat changes to the main form and changes in subforms as one transaction. However it doesn't say you can't treat them seperately as two transactions.
If you can call a subforms methods from the main form then why not have two transactions, one for your subform and one for your main form. When you commit or rollback your transaction on the main form then call the methods on your subform to do the same with the second transaction.
I don't use subforms either. Forms within forms within forms within... ad infinitum... nightmare... actually I saw a form once that used 6 subforms some of which had 2 subforms themselves and that was enough to keep me away....
Give the two transactions a go... in theory it should be possible.
Tim Thornton
Thanks. That's a clear explanation of what's happening. I can't seem to implement it though, probably because I'm missing something basic (am still a relative newbie with Access).
Here's what I've done so far:
FormA is a bound form with record source TableA. On the form are Cancel and Save buttons, along with subforms for child tables TableB and TableC. Here's the relevant code for FormA
Option Compare Database
Option Explicit
Private c As ADODB.Connection
Private Sub Form_Open(Cancel As Integer)
Set c = CurrentProject.Connection
c.BeginTrans
End Sub
Private Sub btnCancel_Click()
c.RollbackTrans
End Sub
Private Sub btnSave_Click()
c.CommitTrans
End Sub
This compiles file, and no error messages appear. However, if I make changes to fields on FormA and/or to the fields on the subforms then click Cancel, the changes are still in effect. Is the fact that I'm using bound forms the issue If that's the case, how can I still accomplish the same thing.
Thanks in advance.
kvieceli
Hi man,
Found this which will tell you whats happening and how to fix it....
http://support.microsoft.com/default.aspx scid=kb;EN-US;223213
Stephen B
Hello,
Yes you can use transactions.
Transactions work at the connection level so you need to work with the connection object which you can do by calling CurrentProject.Connection.BeginTrans when your form loads and CurrentProject.Connection.CommitTrans etc when you need them.
I haven't used transactions with bound forms, I generally use ADO, but it should work fine. Could you let me know if it works ok.
Guiseppi
That link was extremely helpful, although, as noted, the solution doesn't include subforms. At least now I understand the issue a lot better.
Are you saying that, if I were using unbound forms, I could control the entire transaction process, including the subform tables If that's the case, I might want to think about redesigning what I've done so far.