Code:
Private Sub Posted_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.
Dim strMsg As String 'MsgBox message.
'Step 1: Initialize database object inside a transaction.
Set ws = DBEngine(0)
ws.BeginTrans
bInTrans = True
Set db = ws(0)
'Step 2: Execute the append.
strSql = "INSERT INTO InternalPostedPackageArchive ( PackageNumber, Program, FacilityArea, FacilityLocation, ) " & _
"IN ""C:\C:\Documents and Settings\thorcc\Desktop\Database Work.InternalPostedPackageArchive.accdb"" " & _
"SELECT PackageNumber, Program, FacilityArea, FacilityLocation FROM PkgcReceivedandScheduledStatus WHERE (Posted = True);"
db.Execute strSql, dbFailOnError
'Step 3: Execute the delete.
strSql = "DELETE FROM PkgcReceivedandScheduledStatus WHERE (Posted = True);"
db.Execute strSql, dbFailOnError
'Step 4: Get user confirmation to commit the change.
strMsg = "Archive " & db.RecordsAffected & " record(s)?"
If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
ws.CommitTrans
bInTrans = False
End If
Exit_DoArchive:
'Step 5: 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