The following is the basic outline of the code. I hope it will help someone answer my question.
Code:
Private Sub cmdClientUpload_Click()
On Error GoTo Err_DoArchive
Dim ws As DAO.Workspace 'Current workspace (for transaction).
Dim db As DAO.Database 'Inside the transaction.
Dim bInTrans As Boolean 'Flag that transaction is active.
Dim strSql As String 'Action query statements.
' Initialize database object inside a transaction.
Set ws = DBEngine(0)
ws.BeginTrans
bInTrans = True
Set db = ws(0)
''''' If the record is created successfully, the Client will be moved to the archive table.'''''
If MsgBox("This will create Client records. Those not passing validation will not be uploaded." & vbCrLf & vbCrLf & "Are you sure? ", vbOKCancel + vbQuestion, "Uploading") = vbOK Then
If IsNull(txtBatchId) Then
' Create the Batch
strSQL = "INSERT INTO blah blah blah"
CurrentDb.Execute strSQL
' Write the Batch ID to records for tracking
' Only records ready for upload
Dim intBatchId As Integer
Dim intCount As Integer
intBatchId = DMax("BATCH_ID", "tblBatch")
strSQL = "UPDATE tblblah blah blah & _
" WHERE ReadyForUpload = True"
CurrentDb.Execute strSQL
' Load the ID to the form
txtBatchId = intBatchId
End If
'''''' Cycle through all ready records to process them one-by-one'''''
Dim rs As Recordset
strSQL = "SELECT * FROM tbltbl blah blah blah"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
SysCmd acSysCmdInitMeter, "Processing...", rs.RecordCount
intCount = 0
dteEnteredDate = Now()
Do While Not rs.EOF
If IsNull(rs("ClientId")) Then
' Determine some field values
dteReceivedDate = Now()
' Create the Client record
strSQL = "INSERT INTO tbl blah blah blah"
CurrentDb.Execute strSQL
' Write the Client ID back to local table
' Match on Description
intInsertedClientId = DMax("blah", "blah")
strSQL = "UPDATE tbl blah blah blah"
CurrentDb.Execute strSQL
' Update the Client Number
strSQL = "UPDATE tbl blah blah blah"
CurrentDb.Execute strSQL, dbSeeChanges
' Write to Log
strSQL = "tbl blah blah blah"
CurrentDb.Execute strSQL
End If
intCount = intCount + 1
SysCmd acSysCmdUpdateMeter, intCount
rs.MoveNext
Loop
' Generate all other records in SET operations
' Create Client Fund record
strSQL = "blah"
CurrentDb.Execute strSQL
' Create Client Notes
strSQL = "blah"
CurrentDb.Execute strSQL
' Create Client Characteristics
strSQL = "blah"
CurrentDb.Execute strSQL, dbSeeChanges
' Write uploaded timestamp and Client Number
strSQL = "blah"
CurrentDb.Execute strSQL
SysCmd acSysCmdClearStatus
MsgBox intCount & " Clients uploaded!", vbInformation
Call Form_Load 'refreshes current form
End If
Exit_DoArchive:
' Clean up
On Error Resume Next
Set db = Nothing
If bInTrans Then 'Rollback if the transaction is active.
ws.Rollback
End If
Set ws = Nothing
Exit Sub
Err_DoArchive:
MsgBox Err.Description, vbExclamation, "Archiving failed: Error " & Err.Number
Resume Exit_DoArchive
End Sub