Consulting

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
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    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
    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
  •