Excel Hints

Results 1 to 3 of 3

Thread: Begin/Rollback/Commit Transaction for access

  1. #1

    Begin/Rollback/Commit Transaction for access

    Hi,

    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
    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"
    .Open
    .BeginTrans

    '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)"
    .CommitTrans
    .Close
    MsgBox "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[/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

    HTH
    -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

Posting Permissions

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