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