Click here to Skip to main content
15,173,640 members
Articles / Productivity Apps and Services / Microsoft Office
Posted 11 Apr 2007


36 bookmarked

Managing transactions with Access

Rate me:
Please Sign up or sign in to vote.
4.40/5 (10 votes)
23 May 20073 min read
This is an Add-in for Microsoft Access that allows writing and execution of SQL statements with transaction control

Screenshot - ImmediateSQL.jpg


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.


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
        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.LockType = adLockOptimistic
        r.CursorLocation = adUseClient
        OpenTempTable r
        MsgBox "processed rows:" & recno
    End If

    InsertIntoStatements Me.txtStmt.Value

    Exit Sub


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

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

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

    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

    '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 


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

    End If

    'refresh the statements list

End Sub

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


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


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Italy Italy
I'm a senior software developer.
I wrote my first program in basic with commodore 64, that is... a long time ago Wink | ;-)
From that moment, I've learned many programming language and developed many projects.

I've started working as IT consultant in a software factory company that had produced software mostly for banking and financial business.
In this environment I could work on many different hardware platforms, using many different technologies and programming languages.Then, in the era of distributed application, I learnt to make all these different techologies working together.

My interest has always been in software development specially oriented to internet application, but during all this time I've acquired also other skill in system and network administration.

Comments and Discussions

GeneralThanks for this tool Pin
guillemSerra16-Aug-10 23:28
MemberguillemSerra16-Aug-10 23:28 
GeneralSQL Resource Pin
FrankNight21-Aug-07 0:24
professionalFrankNight21-Aug-07 0:24 
QuestionQuestions for How to Use the Tool Pin
MOISJWang15-Aug-07 10:18
MemberMOISJWang15-Aug-07 10:18 
Hi Franknights,

It looks like a good tool that I can use in data archiving. Can you let me know:

1. How do I use the following statement:

"select * from diagsrv where service_date < #01/01/2006# and balance_due = 0 insert into diagsrv_ar"

I can not execute this statement. Even I split this into two SQL, I got an error with break @: Set c = CurrentProject.AccessConnection

2. How do I initialize CurrentProject.AccessConnection?
3. If I want to select data from the local Access table and insert selected data into a different MDB table, how can I accomplish this?

Thanks for your attention and your efforts for this tool.

Jack Wang

Software design/development are state of the arts and fun. Don't lose your dreams and visions.

AnswerRe: Questions for How to Use the Tool Pin
FrankNight20-Aug-07 23:48
professionalFrankNight20-Aug-07 23:48 
GeneralSi parva licet Pin
grovelli25-May-07 9:29
Membergrovelli25-May-07 9:29 
GeneralGreat Idea Pin
Arun.Immanuel23-May-07 5:11
MemberArun.Immanuel23-May-07 5:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.