Transactions in Access 2013

Galina K
Galina K used Ask the Experts™
on
Dear All,
Transactions do not  work in Access 2013 at all: neither Commit, nor Rollback. No Errors, just do not work.
Help, please.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
please post the code you are using, thanks
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
Transactions work in all versions of Access since 2.0 (maybe sooner).
I use them often, including A2010, A2013 & A2016.

Commented:
Thank you for  reply.
I have a form with a tab control. The first page contains the main form, based on Products table. The second page contains a subform with purchase orders for a product, chosen on the first page. The third page contains sales orders for a product, chosen on the first page. The forth page contains inventory record from an Inventory table with inventory info for the chosen product. I have a task to create a Cancel button that rolls back all changes, made on this form. I have read about experiments that programmers made to roll back changes in subforms with linked tables, but I am not sure that it may work. Want to experiment by myself.  The question is that Cancel button does not work even on the main form,  Code:
Option Compare Database
Private wrk As DAO.Workspace
Private db As DAO.Database


Private Sub cmdCancel_Click()
    On Error GoTo Error_Handler
    
    If Me.Dirty Then
       [TempVars]![YesNoMessage] = "Do you want to cancel changes?"
       DoCmd.OpenForm "YesNoAlert", , , , , acDialog
       If [TempVars]![YesNoResponse] = True Then
          wrk.Rollback
       Else
          DoCmd.RunCommand acCmdSaveRecord
          wrk.CommitTrans
       End If
    Else
       wrk.Rollback
    End If
    wrk.Close
    Set wrk = Nothing
    DoCmd.RunCommand acCmdCloseWindow

Sub_Exit:
    Exit Sub

Error_Handler:
    wrk.Rollback
    wrk.Close
    Set wrk = Nothing
    DoCmd.RunMacro "Utilities.ErrorAlert"
    Resume Sub_Exit

End Sub


Private Sub Form_Open(Cancel As Integer)
   On Error GoTo Error_Handler
   
   Set wrk = DBEngine.Workspaces(0)
   Set db = CurrentDb

   wrk.BeginTrans

Sub_Exit:
   Exit Sub

Error_Handler:
    wrk.Rollback
    wrk.Close
    Set wrk = Nothing
    DoCmd.RunMacro "Utilities.ErrorAlert"
    Resume Sub_Exit
    
End Sub
'------------------------------------------------------------
' Form_Current
'
'------------------------------------------------------------
Private Sub Form_Current()
   On Error GoTo Error_Handler
   
   DoCmd.SetProperty "chkDiscontinued", acPropertyVisible, Not (IsNull(txtID))
   DoCmd.SetProperty "sbfInventoryDetails", acPropertyEnabled, Not (IsNull(txtID))
   DoCmd.SetProperty "sbfInventoryDetails", acPropertyLocked, IsNull(txtID)

Sub_Exit:
    Exit Sub

Error_Handler:
    wrk.Rollback
    wrk.Close
    Set wrk = Nothing
    DoCmd.RunMacro "Utilities.ErrorAlert"
    Resume Sub_Exit

End Sub


'------------------------------------------------------------
' Form_Load
'
'------------------------------------------------------------
Private Sub Form_Load()
    On Error GoTo Error_Handler

    DoCmd.OpenForm "EnterPassword", acNormal, "", "", , acDialog
    If (TempVars!tmpCompanyPsw <> TempVars!tmpEnteredPsw) Then
        TempVars.Add "AlertMessage", "You have entered incorrect password"
        DoCmd.OpenForm "OkAlert", acNormal, "", "", , acDialog
        DoCmd.Close acForm, "ProductDetail"
    End If

Sub_Exit:
    Exit Sub

Error_Handler:
    wrk.Rollback
    wrk.Close
    Set wrk = Nothing
    DoCmd.RunMacro "Utilities.ErrorAlert"
    Resume Sub_Exit

End Sub


'------------------------------------------------------------
' cmdSaveClose_Click
'
'------------------------------------------------------------
Private Sub cmdSaveClose_Click()
    On Error GoTo Error_Handler

    If (Form.Dirty) Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    wrk.CommitTrans
    wrk.Close
    Set wrk = Nothing
    DoCmd.Close , ""

Sub_Exit:
    Exit Sub

Error_Handler:
    wrk.Rollback
    wrk.Close
    Set wrk = Nothing
    DoCmd.RunMacro "Utilities.ErrorAlert"
    Resume Sub_Exit

End Sub


'------------------------------------------------------------
' cmdSaveNew_Click
'
'------------------------------------------------------------
Private Sub cmdSaveNew_Click()
    On Error GoTo Error_Handler

    If (Form.Dirty) Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    wrk.CommitTrans
    DoCmd.GoToRecord , "", acNewRec
    DoCmd.GoToControl "txtProductName"
    wrk.BeginTrans

Sub_Exit:
    Exit Sub

Error_Handler:
    wrk.Rollback
    wrk.Close
    Set wrk = Nothing
    DoCmd.RunMacro "Utilities.ErrorAlert"
    Resume Sub_Exit

End Sub


'------------------------------------------------------------
' tc_ProductInfo_Change
'
'------------------------------------------------------------
Private Sub tc_ProductInfo_Change()
    On Error GoTo Error_Handler

    If Form.Dirty Then
       [TempVars]![YesNoMessage] = "Do you want to save changes?"
       DoCmd.OpenForm "YesNoAlert", , , , , acDialog
       If [TempVars]![YesNoResponse] = True Then
          DoCmd.RunCommand acCmdSaveRecord
          wrk.CommitTrans
       Else
          wrk.Rollback
       End If
    Else
       wrk.Rollback
    End If
    wrk.BeginTrans
 
Sub_Exit:
    Exit Sub

Error_Handler:
    wrk.Rollback
    wrk.Close
    Set wrk = Nothing
    DoCmd.RunMacro "Utilities.ErrorAlert"
    Resume Sub_Exit

End Sub


'------------------------------------------------------------
' cmdNewSupplier_Click
'
'------------------------------------------------------------
Private Sub cmdNewSupplier_Click()
    On Error GoTo Error_Handler

    TempVars.Remove "Suppliers_ID"
    DoCmd.OpenForm "SupplierDetails", acNormal, "", "", acAdd, acDialog
    If (Not (IsNull(TempVars!Suppliers_ID))) Then
        DoCmd.SetProperty "cboSupplierID", , TempVars!Suppliers_ID
    End If
    DoCmd.Requery "cboSupplierID"
    TempVars.Remove "Suppliers_ID"

Sub_Exit:
    Exit Sub

Error_Handler:
    wrk.Rollback
    wrk.Close
    Set wrk = Nothing
    DoCmd.RunMacro "Utilities.ErrorAlert"
    Resume Sub_Exit
End Sub

Open in new window


I will resume my work tomorrow, on Monday. Thank you for your help very much.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
Well .. respectfully ... that is a lot of code to pile through which I do not have time for at the moment.
However, here is an example of how Transaction processing code works in Access ... which I have in many applications.
And in fact, Transactions in Access are quite amazing. I have places wherein 15 action queries run inside of a Transaction. If there is ANY failure along the way, *everything* is rolled back ... and no data changes.
Public Function mTransCodeExample() As Boolean

    Const sFx As String = "Module: vbaTrans" & ", " & "Procedure: mTransCodeExample"
    On Error GoTo mTransCodeExample_Error
    Dim wks As DAO.Workspace
    
    'Other code here not related to Transaction
    '====
    
        
    'Transaction processing ...
    On Error GoTo mTransCodeExample_TransError
    wks.BeginTrans
    With wks.Databases(0)
        .Execute "qryZiNem20_AgreementProcessing_Step01", dbFailOnError
        .Execute "qryZiNem20_AgreementProcessing_Step02", dbFailOnError
        .Execute "qryZiNem20_AgreementProcessing_Step03", dbFailOnError
    End With
    wks.CommitTrans

mTransCodeExample_Exit:
    On Error Resume Next
    'clean up
    Set wks = Nothing
    Err.Clear
    Exit Function

mTransCodeExample_Error:
    MsgBox "An unexpected error has occured" & vbCrLf & vbCrLf & _
           "Error Information --------------------" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Description: " & Err.Description & vbCrLf & vbCrLf & _
           "Code Fx: " & sFx & vbCrLf & vbCrLf & _
           "Line Number: " & Erl & vbCrLf & vbCrLf & _
           48, "SomeDescriptionHere"
    Resume mTransCodeExample_Exit

mTransCodeExample_TransError:
    wks.Rollback
    GoTo mTransCodeExample_Error

End Function

Open in new window

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
I see Joe's posted showing how it's done, but I thought I might toss in a few quick points:

1. Transaction statements in VBA were meant to be used only for multi-table updates being done in code as Joe showed.
2. If your using bound forms (record source is set for the form), then Access handles all the DB operations in a work space that you cannot access or manipulate as far as transactions are concerned.
3. If you want to use transactions with sub forms, you'd need to set the record source to a record set you open in code which you can control, and handle all the master/child linking for the sub forms along with those record sets.  That's a lot of work.   If you can land a copy of the Access 2000 Developers Handbook, there is sample code in there for doing all that.
4. Many are not aware, but Access does an automatic save of the main record as soon as you enter a sub form.

 With all that said, for main/subform combinations with the least about of work, the usual technique is to use a set of temp tables for the records.  

1. When the user starts the edit, you copy the records to the temp table.
2. If they cancel, you do nothing.
3. If they "save", then you update all the main table records.

This approach has it's problems too though.

Jim.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
Jim ... I'm also finding out that the same exactly transactions that work against Access tables work somewhat differently against SQL Server tables ... in the context of locking. I have some transactions that are hanging up against SQL Server tables. Some investigation is required.

Commented:
Dear Jim,
Could you post here a sample of code with temp tables or refer me to this sample anywhere? Thank you.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
hi Galina,

Joe and Jim have given you excellent information.  

It seems an assumption is being made that a transaction can start when a form is opened, and can capture everything that is done until ...

Instead of this way of thinking ...  think about completing transactions in the same procedure (ok to call others) ... I say this from what I have read, not done -- defer to others to correct me on this.

If what is wanted is to add/edit a "temporary" set of records, then perhaps the application needs to do that -- and, ideally, store those temporary records in an external file that doesn't cause bloat ... or have some way of marking so unnecessary additions and deletions are not done
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
I think ... the legendary Leigh Purvis might have what you are looking for:

http://www.databasedevelopment.co.uk/examples.htm

Scroll down to
Transactions In Forms (Download FormTransaction.zip)
A test bed showing how ADO and DAO recordsets can wrap bound form functionality.
While not comprehensive or clean - it shows the basics of how to achieve it. For full such control consider a combination of other techniques, e.g. Unbound or Disconnected data.


Lots of other great stuff on his site as well.

Commented:
Thank you. I will look at this example. If I have questions, I will ask you. Thank you one more time.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
Actually ... I wouldn't have time to dive into that per se.  But there is a sample db with it .... so hopefully that will guide you.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<Could you post here a sample of code with temp tables or refer me to this sample anywhere? Thank you.>>

 I will try and dig something out, but I haven't done that in quite some time.  Reason?   You really have to control what Access does because you need to provide a Edit, Cancel, Save buttons, lock/unlock controls, etc. to control it all.

 In the process of doing that, it creates a lot of problems because your forcing Access into something that it normally doesn't do.  As many have found, the more you try to control Access, the more it fights back, and at some point, you have to ask yourself why you are bothering.

 The beauty of Access is that a lot is built-in so you don't have to do the work.   But if your going to bother to do the work and ignore what Access has built-in, then you might as well use something else.   So this is something I haven't bothered to do in a long time.

 I would admit though that this is one area where I wish Access would have been improved.   Microsoft did take a stab at it by making the forms recordset available in Access 2000 (allowing you to do transactions on your own), but it was a half hearted attempt.   If they were serious, they would have wrapped everything internally in a transaction as you are attempting to do and done that automatically for you.

 After all, visually your user has no idea if something is in a subform or not, and doesn't realize that as soon as they jump to one, the main form changes are committed, or why when they hit esc that all their changes are not un-done.  But that is the expectation.

 Microsoft should have looked to Visual Fox Pro, which handled the data environment of a form beautifully, allowing you to choose when and how changes were committed in a very seamless way.

Jim.

Commented:
Dear All,
I have made my forms unbound and do not understand, why the following line   Me.txtID = Forms!ProductsDatasheet!ID in code does not work:

Private Sub Form_Load()
    On Error GoTo Error_Handler

    DoCmd.OpenForm "EnterPassword", acNormal, "", "", , acDialog
    If (TempVars!tmpCompanyPsw <> TempVars!tmpEnteredPsw) Then
        TempVars.Add "AlertMessage", "You have entered incorrect password"
        DoCmd.OpenForm "OkAlert", acNormal, "", "", , acDialog
        DoCmd.Close acForm, "ProductDetail"
    Else
       Me.txtID = Forms!ProductsDatasheet!ID
    End If
   
Sub_Exit:
    Exit Sub

Error_Handler:
    DoCmd.RunMacro "Utilities.ErrorAlert"
    Resume Sub_Exit

End Sub

Thank you in advance

Author

Commented:
I have found a solution for the last question. The control txtID had to be unbound.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial