Author Topic: error when use cursor with ADO Jet database  (Read 3752 times)

mostafa

  • Newbie
  • *
  • Posts: 1
    • View Profile

  • Total Badges: 0
    Badges: (View All)
error when use cursor with ADO Jet database
« on: September 24, 2005, 04:44:08 pm »
I am Using Ado with Jet Database ; I have Database " Experts.mdb" on the server , there are many users access the database from their clients ,I am using the server cursor that to  get the value from table " tblCounter" that table with one field
" NextVal ", And locking  the record by " adLockPessimistic"  to prevent other users to take the same value , I will Increase the value by one and leave the table to anther user to take the new value .

That is the connection Code

Public Conn As ADODB.Connection
Dim mDataPath As String
Private Sub Main()
  Set Conn = New ADODB.Connection
'
 mDataPath = "Data Source=" & "E:\TestMultiUse\DBTest.mdb" & ";Jet _  
                       OLEDB:System Database=" & App.Path & "\Experts.mdw" _
 mDataPath = mDataPath & ";User ID=Admin;Jet OLEDB:Database _
                       Password=km12prm3xgd28"
 With Conn
         .Provider = "Microsoft.Jet.OLEDB.4.0"
   .Open mDataPath
End With
    If Err.Number <> 0 Then
      MsgBox " ======="
    End If
' That is the code to access table tblCounter

Public Function GetNextVal() As Double
On Error Resume Next
Dim RS As ADODB.Recordset
'
Dim I As Integer
Set RS = New ADODB.Recordset
QSql = " Select Nextval from tblCounter"
'
GetNextVal = 0
I = 1
RS.CursorLocation = adUseServer
Err.Number = 0
'
APPSec:
RS.Open QSql, Conn, adOpenDynamic, adLockPessimistic
'
If Err.Number <> 0 Then GoTo ERRORSEC
If RS.EOF = False Then
    GetNextVal = RS!NextVal
    RS!NextVal = RS!NextVal + 1
    RS.Update
 DoEvents

ERRORSEC:
      If Err.Number <> 0 Then
         Err.Number = 0
          RS.Close
          Err.Number = 0
          GoTo APPSec
      End If
   Else
          Err.Number = 0
          GoTo APPSec
End If
RS.Close
Set RS = Nothing
End Function

That is the end of my code ; with sorry this code generate some thing I did not understand when we execute this function with  other users , They take some of the same values , and the ADO did not feel with my locking in the table , I did not know the reason , when I did this code with DAO the result is very good no similar values between me and the other users .

What is the wrong in my code , Is in The Connection Or what
Pls Help me to solve this problem , It is very important for my programs

Mostafa Fiad
Egypt
mlmfiad@yahoo.com