Results 1 to 3 of 3

Thread: Begin/Rollback/Commit Transaction for access

  1. #1

    Begin/Rollback/Commit Transaction for access


    Anyone knows if these exist in access, could you point at a piece of code for this?

    Info: These are SQL transactions which are a bunch of queries, they change your database however if any of the queries fails the whole database reverts back to original state.

    Does anyone know of a way for doing it?

  2. #2
    VBAX Master CreganTur's Avatar
    Jan 2008
    Greensboro, NC
    Transaction processing is handled in Access via ADO (ActiveX Data Objects) connections. Here's an example of an ADO transaction from the Access 2007 Programming By Example book:

    [VBA]Sub CreateTransactionADO()
    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=C:\Acc07_ByExample\Northwind.mdb"

    'insert a new customer record
    .Execute "INSERT INTO Customers " & _
    "Values ('GWIPO','Gwiazda Polarna'," & _
    "'Marcin Garnia', 'Sales Manager', 'ul.Majewskiego 10'," & _
    "'Warsawa', Null, '02-106', 'Poland', '0114822230445', Null)"

    'insert the order for that customer
    .Execute "INSERT INTO Orders" & _
    " (CustomerId, EmployeeId, OrderDate, RequiredDate)" & _
    " Values ('GWIPO', 1, Date(), Date()+5)"
    MsgBox "Both inserts completed."
    End With
    Set conn = Nothing
    Exit Sub
    If Err.Number = -2147467259 Then
    MsgBox Err.Description
    Resume ExitHere
    MsgBox Err.Description
    With conn
    End With
    Resume ExitHere
    End If
    End Sub[/VBA]

    You can get more info from Access Help, as long as you have a reference set to the ADO Object Library.

    Also, to create a connection to your current open database (the database you're writing the code in) with ADO you would use: CurrentProject.Connection

    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003

  3. #3
    Great thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts