Incrementing Transaction ID

[DGULLIVER]

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
                        .Update
                        .Close
                    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
Darren

[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.