• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 16. Transaction Processing > Practical Examples: Using Transaction Proc...

Practical Examples: Using Transaction Processing to Improve the Integrity of Your Applications

As you continue to develop your own applications, you might find it necessary to use VBA code to accomplish certain tasks. These tasks might require that several processes complete successfully or not at all. As these situations arise, you should consider placing them in a transaction loop. An example of such a situation is the frmArchivePayments form, as shown in Figure 16.1. frmArchivePayments enables the user to specify a date range. The code uses this date range as the criterion to determine what data it sends to the tblPaymentsArchive table and removes from the tblPayments table. When the user runs the process, you want to ensure that the process runs in its entirety or not at all. Listing 16.8 shows transaction loop code suitable for this situation.

Listing 16.8. A Transaction Loop Suitable for the Time and Billing Application

Sub cmdArchivePayments_Click()
    On Error GoTo Err_cmdArchivePayments_Click

    Dim cnn As ADODB.Connection
    Dim strSQL As String
    Dim boolInTrans As Boolean

    boolInTrans = False

    Set cnn = CurrentProject.Connection

    cnn.BeginTrans
        boolInTrans = True
        strSQL = "INSERT INTO tblPaymentsArchive" & _
            " SELECT DISTINCTROW tblPayments.* " & _
            " FROM tblPayments " & _
            " WHERE tblPayments.PaymentDate Between #" & _
            Me!txtStartDate & _
            "# And #" & _
            Me!txtEndDate & "#;"
        cnn.Execute strSQL
        strSQL = "DELETE DISTINCTROW tblPayments.PaymentDate " & _
            "FROM tblPayments " & _
            " WHERE tblPayments.PaymentDate Between #" & _
            Me!txtStartDate & _
            "# And #" & _
            Me!txtEndDate & "#;"
        cnn.Execute strSQL
    cnn.CommitTrans
    boolInTrans = False

Exit_cmdArchivePayments_Click:
    Exit Sub

Err_cmdArchivePayments_Click:
    MsgBox Err.Description
    If boolInTrans Then
        cnn.RollbackTrans
    End If
    Resume Exit_cmdArchivePayments_Click

End Sub 


					  


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint