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.