Hi everyone, I have the following code which generates an autonumber. This worked fine before I split my database, but now fails and my error code runs that it can't get a Counter.
The problem seems to be with the locking:
If I change:
Set rst = db.OpenRecordset("tblFlexAutoNum", _
dbOpenTable, dbDenyWrite + dbDenyRead)
to...
Set rst = db.OpenRecordset("tblFlexAutoNum")
...then it works...but obviously without the locking which I want because it's deployed in a multi-user environment.
Can anyone tell me why it fails on a split database but runs OK when the db is not split?
Can anyome tell me a solution so i can implement the locking on the table?
I have checked the ADO/DAO references on the back-end and everything looks OK
Thanks for any help
__________________________________________________________
The problem seems to be with the locking:
If I change:
Set rst = db.OpenRecordset("tblFlexAutoNum", _
dbOpenTable, dbDenyWrite + dbDenyRead)
to...
Set rst = db.OpenRecordset("tblFlexAutoNum")
...then it works...but obviously without the locking which I want because it's deployed in a multi-user environment.
Can anyone tell me why it fails on a split database but runs OK when the db is not split?
Can anyome tell me a solution so i can implement the locking on the table?
I have checked the ADO/DAO references on the back-end and everything looks OK
Thanks for any help
__________________________________________________________
Code:
Public Function acbGetCounter() As Long
' Get a value from the counters table and
' increment it
Dim varField As String
varField = Screen.ActiveForm.Tag
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intLocked As Integer
Dim intRetries As Integer
Dim lngTime As Long
Dim lngCnt As Long
' Set number of retries
Const conMaxRetries = 5
Const conMinDelay = 1
Const conMaxDelay = 10
On Error GoTo HandleErr
Set db = CurrentDb()
intLocked = False
Do While True
For intRetries = 0 To conMaxRetries
On Error Resume Next
Set rst = db.OpenRecordset("tblFlexAutoNum", _
dbOpenTable, dbDenyWrite + dbDenyRead)
If Err = 0 Then
intLocked = True
Exit For
Else
lngTime = intRetries ^ 2 * _
Int((conMaxDelay - conMinDelay + 1) * Rnd + conMinDelay)
For lngCnt = 1 To lngTime
DoEvents
Next lngCnt
End If
Next intRetries
On Error GoTo HandleErr
If Not intLocked Then
If MsgBox("Could not get a counter: Try again?", _
vbQuestion + vbYesNo) = vbYes Then
intRetries = 0
Else
Exit Do
End If
Else
Exit Do
End If
Loop
If intLocked Then
acbGetCounter = rst(varField)
rst.Edit
rst(varField) = rst(varField) + 1
rst.Update
rst.Close
Else
acbGetCounter = -1
End If
Set rst = Nothing
Set db = Nothing
ExitHere:
Exit Function
HandleErr:
MsgBox Err & ": " & Err.Description, , "acbGetCounter"
Resume ExitHere
End Function
Last edited by a moderator:
