Sub Create_Transaction()
Dim conn As ADODB.Connection
On Error GoTo ErrorHandler
Set conn = New ADODB.Connection
With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source = " & CurrentProject.Path & "\mydb.mdb"
.Open
.BeginTrans
.Execute "INSERT INTO Customers Values('A','P','M', 'Manager', 'M 10','W', Null, '02-111', 'Vancouver', '0000000000000', Null)"
.Execute "INSERT INTO Orders (CustomerId, EmployeeId, OrderDate, RequiredDate) Values ('G', 1, Date(), Date()+5)"
.CommitTrans
.Close
Debug.Print "Both inserts completed."
End With
ExitHere:
Set conn = Nothing
Exit Sub
ErrorHandler:
If Err.Number = -2147467259 Then
MsgBox Err.Description
Resume ExitHere
Else
MsgBox Err.Description
With conn
.RollbackTrans
.Close
End With
Resume ExitHere
End If
End Sub