Access Cookbook - Ken Getz [133]
Discussion
The heart of this technique is the acbGetCounter function. This function tries to open the tblFlexAutoNum table exclusively and, if it succeeds, gets the value in the CounterValue field and increments the stored value by some fixed number. The retrieved value is then returned to the calling procedure. acbGetCounter is shown here:
Public Function acbGetCounter( ) As Long
' Get a value from the counters table and
' increment it
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim blnLocked As Boolean
Dim intRetries As Integer
Dim lngTime As Long
Dim lngCnt As Long
Dim lngCOunter As Long
' Set number of retries
Const conMaxRetries = 5
Const conMinDelay = 1
Const conMaxDelay = 10
On Error GoTo HandleErr
Set db = CurrentDb( )
blnLocked = False
Do While True
For intRetries = 0 To conMaxRetries
On Error Resume Next
Set rst = db.OpenRecordset("tblFlexAutoNum", _
dbOpenTable, dbDenyWrite + dbDenyRead)
If Err.Number = 0 Then
blnLocked = 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 blnLocked 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 blnLocked Then
lngCOunter = rst("CounterValue")
acbGetCounter = lngCOunter
rst.Edit
rst("CounterValue") = lngCOunter + 1
rst.Update
rst.Close
Else
acbGetCounter = -1
End If
Set rst = Nothing
Set db = Nothing
ExitHere:
Exit Function
HandleErr:
MsgBox Err.Number & ": " & Err.Description, , "acbGetCounter"
Resume ExitHere
End Function
After declaring several variables, acbGetCounter attempts to open a Recordset object on the tblFlexAutoNum table. By specifying the dbDenyRead and dbDenyWrite constants as the Options argument to the OpenRecordset method, it attempts to lock the table exclusively, preventing other users from reading or writing to the table. You can use the dbDenyRead and dbDenyWrite options only with table-type recordsets, so if the table is in an external database you'll need to open the recordset using OpenDatabase, as shown earlier in this chapter.
The function attempts to obtain a lock on the acbcAutoNumTable by using a common multiuser coding construct: a retry loop. The retry loop from acbGetCounter is shown here:
For intRetries = 0 To conMaxRetries
On Error Resume Next
Set rst = db.OpenRecordset("tblFlexAutoNum", _
dbOpenTable, dbDenyWrite + dbDenyRead)
If Err.Number = 0 Then
blnLocked = 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
Note what happens if the lock is not immediately obtained. The procedure calculates a long number based on the number of retries, the acbcMaxDelay and acbcMinDelay constants that were set at the beginning of the function, and a random number. This calculated number, lngTime, is then used to waste time using a For...Next loop that simply counts from 1 to lngTime. We placed a DoEvents statement inside the loop so that Access will process any screen activity during this dead time.
The retry loop and the time-wasting code force the function to pause briefly before attempting to obtain the lock again. Because this function is meant to work in a multiuser situation, it's important that retries are not repeatedly attempted without waiting for the lock to be released. acbGetCounter includes a random component to lngTime that gets larger with each retry to separate out multiple users who might be trying to obtain the lock at the same time.
If the function cannot lock the table after the number of retries specified by the acbcMaxRetries constant, it displays a message box allowing