MS Access 2010: Adding transaction management into a form

Question

MS-Access 2010 environment seems unstable after the use of transactions (BeginTrans/CommitTrans/Rollback). By unstable I mean that MS-Access environment does not allow any object modification anymore (it turns automatically into a kind of exclusive mode), or shows unexpected error messages while running any SQL queries of the process.

Situation:

I have a MS-Access 2010 database (accdb format) where I've added a new VBA module. This module processes a given file in order to load its records in a table in the database. I use the MS-Access 2010 environment to work with this DB (not a separate application)

For each record, some validations are made before it is stored on db. If any error is detected all the processing is canceled.I use transactions to ensure the complete file is ok and loaded.

Problem:

1. If the first time I run this process it is correct and so it finishes with COMMIT, the process can be rerun with other files without matter the result of their processing. The result of each processing only depends on the quality of the data in the file.

2. But if the first time I run this process it finishes with a ROLLBACK , every next run of the process ends with errors while executing any SQL query in the process.

In both cases, MS-Access environment turns into a kind of exclusive mode.

Questions:

Is that kind of VBA module or processing compliant with the MS-Access 2010 development environment? or should I build a new separate application that connects to my Access database to run my file processing.

Code (simplified):

Private Sub Comando0_Click() 'A success processing simulation
    On Error GoTo ErrManagenent

    BeginTrans

    'Some procesing finishin OK
    ProcessWithoutError

    CommitTrans
    Exit Sub

ErrManagenent:

    Debug.Print "Comando0_Click Error: " & Err.Description & "." & vbCrLf & Err.Source

    Rollback
End Sub


Private Sub Comando6_Click() 'A fail processing simulation. 
    On Error GoTo ErrManagenent

    BeginTrans

    'Some procesing finishin with a Err.Raise
    ProcessWithError

    Exit Sub

ErrManagenent:

    Debug.Print "Comando6_Click Error: " & Err.Description & "." & vbCrLf & Err.Source

    Rollback

End Sub


Private Sub ProcessWithError() 'simulation of a process ending with error
    Dim rs As Recordset
    Dim strSql As String

    'Any DB query.
    strSql = "SELECT * FROM 00_Bancos"
    Set rs = CurrentDb.OpenRecordset(strSql)

    Debug.Print rs.RecordCount

    'some processing with
    '......

    'let's suppose there is an error while processing
    rs.Close
    Err.Raise 11, , "MY error mesage"

End Sub


Private Sub ProcessWithoutError() 'Simulation of a process ending OK
    Dim rs As Recordset
    Dim strSql As String

    'Any DB query.
    strSql = "SELECT * FROM 00_Bancos"
    Set rs = CurrentDb.OpenRecordset(strSql)

    Debug.Print rs.RecordCount

    'some processing 
    '......

    'let's suppose the process finishes OK.
    rs.Close

End Sub

Answer 1

you should use transactions only if you are inserting/updating multiple SQL statements which then make sense to for rollback. Also use transactions just before SQL execution and trap errors to find out which SQL statement triggers the failure.

pseudo:

1. Do the validation
2. Prepare SQL statements
3. Prepare SQL Statement 2
4. Begin Transaction
5. Execute SQL statements
6. Commit or rollback

in code it would be:

Private Sub mTrans()

    Dim myDB As DAO.Database
    Set myDB = CurrentDb

    Dim SQL_SET As String
    SQL_SET = "First sql statement"

    On Error GoTo ERROR_SQL1:
    DBEngine.BeginTrans
        myDB.Execute SQL_SET, dbFailOnError
        On Error GoTo ERROR_SQL2:
        SQL_SET = "second sql statement..." 'either use the same variable or use SQL_SET1 for better overview
        myDB.Execute SQL_SET, dbFailOnError
    DBEngine.CommitTrans

EXIT_SUB:
    On Error Resume Next
    Set myDB = Nothing
    Exit Sub
ERROR_SQL1:
    DBEngine.Rollback
    MsgBox "Error while executing sql_1. " & vbNewLine & "System msg: " & Err.description
    GoTo EXIT_SUB

ERROR_SQL2:
    DBEngine.Rollback
    MsgBox "Error while executing sql_2. " & vbNewLine & "System msg: " & Err.description
    GoTo EXIT_SUB
End Sub