I have two class modules namely DbConnection and Database.

Database has all methods which are required to manipulate the tables (Insert, update, delete, move).

And DbConnection has connection related methods (Connect, Close, StartTrans, CommitTrans, RollbackTrans)

I have a global instance of DbConnection in a standard module and I don't initiate any other instance of the same anywhere in the code.

But I initiate many instances of Database each for a table.

The code for the transaction methods in DbConnection.cls is as follows:

Public Sub startTrans()
    
    Connection.BeginTrans
    
    hasActiveTrans = True
        
End Sub

Public Sub commitTrans()
    
    On Error GoTo rollbacktrns
    
    Connection.commitTrans
    
    MsgBox Connection.Errors.count
    
    MsgBox Err.Description
    
    hasActiveTrans = False
    
    Exit Sub
    
rollbacktrns:
    
    rollBack
    
End Sub

Public Sub rollBack()
    
    Connection.RollbackTrans
    
    hasActiveTrans = False
    
End Sub

I have a form in which I insert data into two tables, so I require a transaction as the tables are interdependent.

The code after vb's data verification is as follows:

If VerifyData(Account, Personal) = True Then
        
        DbCon.startTrans
    
        Db_Account.RowInsert SplitArray(Account, 1), SplitArray(Account, 2)
    
        Db_Personal.RowInsert SplitArray(Personal, 1), SplitArray(Personal, 2)
        
        DbCon.commitTrans
    
    End If

All works fine till the second last line in the If structure.

By debugging I found out that there's some error occurring when commitTrans and it jumps to rbacktrans label in the sub-procedure as defined in the DbConnection.cls module

But when I check Connection.Errors.Count, it is zero and also Err.Description is NULL.

What should I do ? Where is the error actually. And I entered all valid values in the fields of the form (not violating any constraint of the database). Nowhere these values are modified in code.

Please help !

What is your db back-end?

It is PostgreSQL and it will be changing to Oracle after finish of project.

I solved the problem. I didn't know that we had to requery the table after starting a transaction because in my previous experience in PHP-MySQL, I never used to do that.

This question has already been answered. Start a new discussion instead.