Concurrency/Consistency Managing in VB6

Dear Pros,

Im a Visual Basic Programmer with experience of about 12 years. I do my application using VB as front end and Oracle as back end. However, i had never deployed in a highly concurrent enivironment where many users use the application at the same time. I am in the process of implementing but have got challenges as the application keep on throwing errors and not completing the transactions. The following are the Errors that are frequently thrown which i suspect are as a result of Concurrency request:

"cannot update row as the data in the database has changed"
"cannot delete row as data in the database has changed"

This is how im connecting to my DB

If RsCoin.State = 1 Then
RsCoin.Close
End If
CmdCoin = "SELECT * FROM COINAGER WHERE ACCNO='" & CoinSt & "'"
RsCoin.Open CmdCoin, ConACExp, adOpenStatic, adLockOptimistic

RsCoin is a recordset

Can somebody assist me how to solve this problem?

where are you getting the following error ?
"cannot update row as the data in the database has changed"
"cannot delete row as data in the database has changed"

I am getting the error at a point of updating.

RsCoin.update

Soo many users are updating that table. its a banking environment.

Can you post the Sub where the Code that error occurs... And Please use Code Tags...
[code] Your Code [/code]...

Code:

---

Public Sub Create_TransactionID()
'On Error Resume Next
Dim SavedDate, CurrDateNo
    'Creating Absence ID
    If RsRef.EOF = False Then
      RsRef.MoveLast
      SavedAbid = RsRef!TRANSREF
      SavedDate = RsRef!TRANSREF
     
      RsRef.Delete 'So that we only have one serial at ago 'Added on 12/11/07
      RsRef.Requery
      RsRef.AddNew
      DateSt = Now
     
      If Len(SavedDate) = 8 Then
          SavedDate = Mid(SavedAbid, 1, 8)
      Else
        SavedDate = SavedDate
      End If
 
      Call Convert_DateTo_Number
      CurrDateNo = Mwaka & Mwezi & Enyanga
     
    '  If SavedDate = CurrDateNo Then 'silenced 15032010
      If Left(SavedAbid, 8) = CurrDateNo Then
          Katikati = Mid(SavedAbid, 9, 1000)
          Katikati = Katikati + 1
        Else
          Katikati = "0"
      End If
             
      Absid = Mwaka & Mwezi & Enyanga & Katikati
      RsRef!TRANSREF = Absid
      RsRef.Update 'Here error occurs
     
      Else
      DabID.AddNew
      Katikati = "0"
      DateSt = Now
      Call Convert_DateTo_Number
      Absid = Mwaka & Mwezi & Enyanga & Katikati
      RsRef!TRANSREF = Absid
      RsRef.Update
     
    End If
   
    If TranT = "Deposit" Then
      Absid = "DP" & Absid
      Else
      If TranT = "Withdrawal" Then
          Absid = "WR" & Absid
        Else
          If TranT = "OC" Then
            Absid = "OC" & Absid
          Else
            If SharesT = True Then
                Absid = "SH" & Absid
              Else
              If Loan = True Then
                  Absid = "LN" & Absid
                Else
                If MpesaT = True Then
                    Absid = "MP" & Absid
                  Else
                  If IntProc = True Then
                      Absid = "IN" & Absid
                    If Treasury = True Then
                      Absid = "TR" & Absid
                    End If
                  End If
                End If
              End If
            End If
          End If
        End If
    End If
End Sub

---

In a multi user environment you should move away from the rs.update and use a sql query to insert, update or delete instead.

I think that should slove the issue, I ran into something similar in the past where I had heavy traffic inserting data and the SQL Insert was flawless where rs.update failed with an error often.