Microsoft Excel Webinar

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:

    VB:
    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 
    
    
    Formatting tags added by mark007
    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
  •