Good morning,

I have 2 client applications which do the same thing. The first app which is written in VBA(MS ACCESS) and the other is ASP.Net.

We can have multiple items for a client so we use a transaction id. When the access application starts a new transaction the app gets a new transaction id and increments by 1 as follows.    

    If VH_TRANSACTION = 0 Then ' New voucher get next transaction number
                    With rst1
                        .Open "SELECT CONF_TRANSACTION FROM tblConfiguration WHERE CONF_CO_ID = " & gblUserInfo.AgencyCountry, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
                         !CONF_TRANSACTION  = Nz(!CONF_TRANSACTION) + 1
                        gblTRANSACTION =  !CONF_TRANSACTION
                    End With
            End If

So it appears that even thought this code is executed the new Transaction ID does not actually update on the database, then when a new transaction is entered it takes the same Transaction ID so it looks like the two transactions are the same.

At first I thought it was just an issue with out web application but I found a case where the VBA application did exactly the same.

Can anyone pease advise how I can make sure the Transaction ID is incremented. This code does work but there is a small percentage when it does not.

My Database is mysql.

Thank you

2 Years
Discussion Span
Last Post by rubberman

You need to issue a begin transaction and commit transaction statements before/after the actual SQL update statement. That will protect you from other activities from interfering with your transaction.

That will also guarantee that your update gets written to the database in a serialized fashion. First in, first updated.

Edited by rubberman

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.