Managing transactions with Access

• Download source files - 46.1 KB

Introduction

This project is an Access 2003 add-in written in VBA. It allows users to write and to execute subsequent DML SQL statements with transaction control. This tool is useful when you want to modify data using SQL queries that affect multiple records in multiple tables.
At the end, after a careful check, you can commit the entire transaction. Modifications in Microsoft Access records are committed by moving across fields or rows and, very often, rollback is impossible. This add-in avoids this behavior. The users can also have a preview of the affected changes over multiple tables before committing the work.

Background

Often, modification queries do not produce intended results. In this case, if the user doesn't have a backup copy of the database, you could have serious problems trying to recreate the initial state. I have crreated an MDA Access add-in which permits the writing of SQL statements (SELECT, INSERT, UPDATE, DELETE) and their execution within a transaction, which can be committed or discarded.

Using the code

This Addin is self-registering. To use it, you simply have to:

1. copy the .mda file in the Microsoft ACCESS directory where msaccess.exe is located
2. open Microsoft Access 2003 or above
3. go to Tools->Add-ins->Add-in Manager
4. browse for the .mda file
5. click OK

You can view the loaded add-in "ImmediateSQL" under the Tools->Add-ins menu. You can browse and change the source code using the internal VBA Macro Editor. At the top of the main window, as shown in the above figure, there is a label indicating the transaction state. At the top-right, there is a button that shows the last 15 statements executed. You can recall them by clicking on them.

You can write DML statements (SELECT, INSERT, UPDATE, DELETE) and execute them by clicking on the "Execute" button or by pressing F5. The transaction begins at the first execution. To see the transaction in action, you have to modify your table with a massive update query and execute a "select *" statement to view record changes. Then you can compare with the original table opened from the tables panel in the database window.

The changes are invisible in the original table or to other users until you have committed the transaction after using the DB. It works with all kind of tables, including connected ODBC, and the transaction also works across multiple databases.

Points of interest

The view management is very simple. It is interesting code that executes statements:

Private Sub ExecuteStatement()
    If IsNull(Me.txtStmt.Value) Or Trim(Me.txtStmt.Value) = "" Then
        MsgBox "No statement to execute", vbExclamation
        Exit Sub
    End If

    If Not isInTransaction Then
        Conn.BeginTrans
        isInTransaction = True
    End If

    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = c
    cmd.CommandText = Me.txtStmt.Value
    On Error GoTo Errore
    Dim r As ADODB.Recordset
    Set r = cmd.Execute(recno)
    On Error GoTo 0
    If r.State <> adStateClosed Then
        r.Close
        r.LockType = adLockOptimistic
        r.CursorLocation = adUseClient
        r.Open
        OpenTempTable r
    Else
        MsgBox "processed rows:" & recno
    End If

    InsertIntoStatements Me.txtStmt.Value

    UpdateDisplay
    Exit Sub

Errore:

    MsgBox "Error:" & Err.Number & vbCrLf & Err.Description, vbCritical
    UpdateDisplay

End Sub

The connection is set when the window is loaded. Then the statement is executed with an ADODB.Command. The Transaction is managed through ADODB.Connection. If the statement returns a recordset, then the results are displayed by calling the OpenTempTable subroutine. The statement executed is registered into an internal table "_Statements" by calling the InsertIntoStatements subroutine.

The OpenTempTable subroutine is interesting because it uses some internal Access functions to create a temp form. The sub adds as many textboxes as fields in the recordset and then binds them to the transacted recordset. At the end, the records are shown as a datasheet to seem like a true Access table:

Private Sub OpenTempTable(r As ADODB.Recordset)

    Dim frm As Form
    Dim frmname As String

    frmname = "_tmpForm"

    'Make a copy of form2 which is used as a template
    On Error Resume Next
    DoCmd.Close acForm, frmname
    DoCmd.DeleteObject acForm, frmname
    On Error GoTo 0
    DoCmd.CopyObject , "_tmpForm", acForm, "Form2"

    'Open the _tmpForm in design mode to allow editing
    DoCmd.OpenForm frmname, acDesign, , , , acHidden

    'Add a bound text box for each fields  
    For Each f In r.Fields
        With CreateControl(frmname, acTextBox)
             ' set control properties
             .Name = f.Name
             .Properties("ControlSource") = f.Name
        End With
    Next

    DoCmd.OpenForm frmname, acFormDS
    Set Forms(frmname).Recordset = r
    Forms(frmname).Refresh

    DoCmd.Save acForm, frmname

End Sub

InsertIntoStatements subroutine shows how to access add-in hidden tables through CodeProject.AccessConnection rather than CurrentProject.Connection or CurrentProject.AccessConnection. Firstly, the subroutine checks if the statement is different from the last statement executed. If it is different, you have to insert the statement in an internal table. Then, if there are more than 15 statements, you have to delete the older one:

Private Sub InsertIntoStatements(stmt As String)

    Dim r As ADODB.Recordset
    Set r = New ADODB.Recordset

    'Check that the statement has not already inserted
    r.Open "SELECT count(1) AS cntr FROM _Statements WHERE date = _
    (SELECT MAX(DATE) FROM _Statements) AND STATEMENT = """ & stmt _
    & """", CodeProject.AccessConnection 
    If r!cntr > 0 Then
        Exit Sub
    End If
    r.Close

    'Insert into statements
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.CommandText = "INSERT INTO _Statements VALUES _
                (Now(), """ & _stmt & """ )"
    cmd.ActiveConnection = CodeProject.AccessConnection 

    cmd.Execute

    'if there are more than 15 statements
    r.Open "SELECT count(1) AS cntr FROM _Statements", _
        CodeProject.AccessConnection 
    If r!cntr > 15 Then
        'delete the oldest
        cmd.CommandText = "DELETE FROM _Statements WHERE date = _
                (SELECT MIN(date) FROM _Statements)"
        cmd.ActiveConnection = CodeProject.AccessConnection 

        cmd.Execute
    End If

    'refresh the statements list
    Me.List9.Requery

End Sub

I think this tool could be evolved in order to create and automate Database scripts to upgrade, clean or move data.

History

• 12 April, 2007 - Original version posted
• 23 May, 2007 - First article update