Using a Database Transaction to Insert Records : Transaction « Access « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Access
3. Transaction

Using a Database Transaction to Insert Records

 
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

 

Related examples in the same category