Transaction Processing

 

Access Transaction Processing

Rollback Begintrans CommitTrans

This Access transaction processing example shows you how to assure that all or no tables get updated in a multi-table transaction.   The setup for this example is as follows:  We have a form which lists customer orders of Widgets.These orders get faxed to the manufacturer and then we want the program to update and clear out various tables to indicate that the faxes have been done and when they were done.

There are basically three tables involved in our Access transaction processing tutorial:

  1. M_Odr_Dtl – containing each Widget order

  2. T_Mnu_Odr – a temporary table used to selectively send out Widget orders to any of several Widget manufacturers.

  3. M_Mnu_Odr – a table that stores the details of each order that has been faxed to the manufacturers.

The transaction process goes as follows:  (Note that a procedure for faxing the orders has already taken place)

  1. Check to see if orders have been faxed

  2. User is asked for a faxed date – usually this is today

  3. Insert each faxed order into our M_Mnu_Odr table

  4. Then we update a field in the M_Odr_Dtl table with each of the newly create M_Mnu_Odr records, creating a link between these two tables

  5. Clear out the appropriate records in the T_Mnu_Odr table

The visual basic code uses DAO.  The code for ADO would be very similar.  The key to all of this is the nesting of the VB BeginTrans instructions and the VB Rollback code in each of the error handlers.   Ideally I would have indented the nested transactions but I don’t have enough room on the screen to make it readable.

Private Sub Command4_Click()Dim strInput As String, myformat As String, Thedate As Date
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim rst4 As DAO.Recordset
Dim db As DAO.Database
Dim wksp As DAO.Workspace
Dim sqltext As String
‘
Set db = CurrentDb
Set rst = db.OpenRecordset(“select count(faxed) as icount from t_Mnu_Odr ” & _
“where faxed=true and Manufacturer_ID=” & Me.Manufacturer_ID)
‘
rst.MoveFirst
If rst!icount = 0 Then
MsgBox “You have not faxed these orders yet.  They will not be marked as done.”
rst.Close
Exit Sub
End If
rst.Close
‘
myformat = Format(Date, “mm/dd/yyyy”)
‘
strInput = InputBox(“Today’s Date or enter another date”, “Faxed Date”, myformat)
If strInput = “” Then
MsgBox “This function has been canceled.”
Exit Sub
End If
‘
On Error GoTo bad_date
Thedate = CDate(strInput)
GoTo Good_date
bad_date:
MsgBox Err.Description & vbCrLf & ”  The date you entered may not be in a standard ” & _
“format.  Try mm/dd/yyyy.  This operation has been canceled”
‘
Resume exit_it
‘
Good_date:
‘
Set wksp = DBEngine.Workspaces(0)
‘
‘————————– setup for 1st VBA transaction processing loop ——————–
‘ start by getting the list of order details to insert into M_Mnu_Odr table
‘
sqltext = ” SELECT T_Mnu_Odr.Manufacturer_ID, ” & _
“T_Mnu_Odr.Order_Detail_ID,” & _
“#” & Thedate & “# as tdate FROM T_Mnu_Odr ” & _
“WHERE T_Mnu_Odr.Manufacturer_ID=” & _
Me.Manufacturer_ID
‘
‘ we don’t need VB transaction processing on the next recordset because we
‘ are only reading it
‘
Set rst3 = db.OpenRecordset(sqltext, dbReadOnly)
‘
‘now open up the m_Mnu_Odr table to insert new recs.
‘
Set rst4 = db.OpenRecordset(“M_Mnu_Odr”, dbOpenDynaset)
wksp.BeginTrans
On Error GoTo roll0
‘
rst3.MoveFirst      ‘ goto the first record that needs to be inserted
Do While Not rst3.EOF
With rst4
.AddNew
!Manufacturer_ID = rst3!Manufacturer_ID
!Order_Detail_ID = rst3!Order_Detail_ID
!Order_Sent_Date = rst3!tdate
.Update
End With
rst3.MoveNext
Loop
‘————————— finished 1st transaction —————————-
GoTo Tran
‘ ———————–  1st transaction error handler ————————-
roll0:
‘ encountered error in first transaction so rollback transactioin
wksp.Rollback
MsgBox Err.Description & ”  An error was encountered will attempting to insert ” & _
“the fax records in table:M_Mnu_Odr.  This operation has been canceled ” & _
“and faxes have NOT been marked as done.”
rst3.Close
rst4.Close
wksp.close
Resume exit_it
‘——————————————————————————–
‘
Tran:
‘—————————– setup 2nd transaction —————————-
‘ update the M_Odr_Dtl.manufacturer_order_id to the newly
‘  inserted Manufacturer_order_ID
‘
On Error GoTo roll1
‘
sqltext = “SELECT M_Odr_Dtl.Manufacturer_Order_ID as Order_Man_ID, ” & _
“M_Mnu_Odr.Manufacturer_Order_ID as Man_ID” & _
” FROM (M_Odr_Dtl INNER JOIN M_Mnu_Odr ON ” & _
“M_Odr_Dtl.Order_Detail_ID = M_Mnu_Odr.Order_Detail_ID) ” & _
“INNER JOIN T_Mnu_Odr ON M_Mnu_Odr.Order_Detail_ID = ” & _
“T_Mnu_Odr.Order_Detail_ID ” & _
” WHERE (((T_Mnu_Odr.Faxed)=True))”
‘
Set rst = db.OpenRecordset(sqltext, dbOpenDynaset)
‘
wksp.BeginTrans
‘
rst.MoveFirst
Do While Not rst.EOF
With rst
.Edit
!Order_Man_ID = !man_id
.Update
.MoveNext
End With
Loop
GoTo tran2
‘—————————- error handler for 2nd transaction —————–
roll1:
MsgBox Err.Description & vbCrLf & ”  Error in updating ” & _
“M_Odr_Dtl.Manufacturer_order_id.  The faxes have not been ” & _
“marked as complete. Perhaps one of these orders is being editted?”
wksp.Rollback

wksp.Rollback
rst.Close
rst3.Close
rst4.Close
wksp.close
Resume exit_it
‘———————————————————————————
tran2:
On Error GoTo roll2
‘
‘——————————– setup for 3rd transaction ———————-
‘ clear out temp table for this manufacturer
‘ first select the records to be deleted
‘
sqltext = “select order_detail_id from T_Mnu_Odr where ” & _
“manufacturer_id=” & Me.Manufacturer_ID
‘
Set rst2 = db.OpenRecordset(sqltext, dbOpenDynaset)
‘
wksp.BeginTrans
‘
rst2.MoveFirst
Do While Not rst2.EOF
With rst2
.Delete
.MoveNext
End With
Loop
‘——————————– finished 3rd Access transaction ————————
wksp.CommitTrans
wksp.CommitTrans
wksp.CommitTrans
‘
rst.Close
rst2.Close
rst3.Close
rst4.Close
wksp.close
GoTo do_report
‘——————————- error handler for 3rd Access transaction —————–
roll2:
MsgBox Err.Description & vbCrLf & ”  Error clearing out Temp table.  The faxes have not ” & _
“been marked as complete. Perhaps one of these orders is being edited?”
wksp.Rollback
wksp.Rollback
wksp.Rollback
rst.Close
rst2.Close
rst3.Close
rst4.Close
wksp.close
Resume exit_it
‘———————————————————————————-
exit_it:
exit sub
do_report:
…
‘ report code follows – but not important for this example

One helpful feature included in this example is the construction of the SQL statements within the code.  This makes seeing what is going on much easier and also aids in debugging since you can have a debug.print sqltext statement to see exactly what is going on.  The way most developers do this is with a stored query often resulting in dozens, if not hundreds, of queries to look through in the database.  Only use stored queries when queries are going to be used in more than two locations in the forms or modules.

Microsoft Office:
MS Access 2000 Through 2016 and Office 365 & Sharepoint

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016