Do we have transactions in MS-Access?
Nobody has actually given you any code examples here in the answer or even cited an example (the Access help files do include examples, though). The key issue to keep in mind is that in Jet/ACE (Access does not support transactions itself -- it depends on whatever database engine you're using for that) that the transaction is controlled at the workspace level. You can create a new workspace for your transaction or create a new one. Here's some sample code:
On Error GoTo errHandler
Dim wrk As DAO.Workspace
Dim db As DAO.Database
Dim lngInvoiceID As Long
Set wrk = DBEngine.Workspaces(0)
Set db = wrk.OpenDatabase(CurrentDb.Name)
With wrk
.BeginTrans
db.Execute "INSERT INTO tblInvoice (CustomerID) VALUES (123);", dbFailOnError
lngInvoiceID = db.OpenRecordset("SELECT @@IDENTITY")(0)
db.Execute "INSERT INTO tblInvoiceDetail (InvoiceID) VALUES (" & lngInvoiceID & ")", dbFailOnError
.CommitTrans
Debug.Print "Inserted Invoice header and detail for Invoice " & lngInvoiceID
End With
exitRoutine:
If Not (db Is Nothing) Then
db.Close
Set db = Nothing
End If
Set wrk = Nothing
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description, vbExclamation, "Error in transaction"
wrk.Rollback
Resume exitRoutine
(code tested and working within Access)
It looks like we do: MSDN - TRANSACTION Statement (Microsoft Access SQL)
Transactions are not started automatically. To start a transaction, you must do so explicitly using:
BEGIN TRANSACTION
Conclude a transaction by committing all work performed during the transaction:
COMMIT [TRANSACTION | WORK]
Conclude a transaction by rolling back all work performed during the transaction:
ROLLBACK [TRANSACTION | WORK]