Unanswered: Trap Error and Retry after a time interval ??????
Here is my problem. I have code that needs a lock on a table before it opens it and makes a change. I have error trapping detecting this, but if it does find an error (such as another user is currently using the table) I want my code to wait a couple of milliseconds and then try again. After 5 tries or so, just give a message and stop everything. Below is my code. Can anyone help me out here? Thanks. Jeremy
P.S. I also attached the file.
Public Function GetNextID() As Long
' this uses a query def, which means it can return a recordset with more than one record
Dim db As Database
Dim qdfFindNextID As QueryDef
Dim rstNextID As Recordset
Dim lngNextID As Long
' Open a database from which QueryDef objects can be
Set db = CurrentDb()
' Create a temporary QueryDef object to retrieve
' data from the database.
Set qdfFindNextID = db.CreateQueryDef("")
.sql = "SELECT NextID FROM NextID WHERE key = 1;" ' always use "key=1"
' TO PUT READ AND WRITE LOCKS ON RECORD, USE THIS:
On Error GoTo ErrorHandler ' if locking error, allow it to be handled
Set rstNextID = .OpenRecordset(dbOpenDynaset, dbDenyRead + dbDenyWrite, dbPessimistic) ' set lock options here
' !!!!!!!!!!!!! MUST TRAP ERROR HERE... IF CANNOT LOCK, LOOP AND RETRY
' First, grab !NextID from rst
' Second, add 1 to !NextID and update record with key=1
' Finally, return the id to be used for this new call record
lngNextID = CLng(!NextID)
.Edit ' first select edit mode
!NextID = CLng(!NextID + 1) ' make changes
.Update ' commit changes in buffer to disk
rstNextID.Close ' unlocks here
GetNextID = lngNextID ' assign next value to function's return value
' Display error information.
MsgBox "(From " & Err.Source & ") Error number " & Err.Number & ": " & Err.Description
' Resume with statement following occurrence of error.
Select Case Err.Number
Case 3218, 3260, 3188, 3186, 3202, 3027
nRetry = nRetry + 1
If nRetry < 10 Then
UpdateLog "Routine Name: Lock error: " & Err.Number & " retry #" & nRetry
For nYY = 0 To 1000
nRetry = 0
nYY = MsgBox("A record is locked by another user. Press Y to try again or No to skip.", vbCritical + vbYesNo, "Record locked.")
If nYY = vbYes Then
UpdateLog "Routine Name: Lock error: " & Err.Number & " re-tried"
UpdateLog "Routine Name: Lock error: " & Err.Number & " Resume Next"
Thanks for the input. What specifically do those error codes handle? Should I leave those in? The error I am getting is # 3008: "The table NextID is already opened exclusively by another user........." Would your error codes pertain to my problem?
I just tried it and it works freaking awesome! My only problem I need to fix now is when they hit "NO" to not retry again it says "Error # 0" and then "Error Resume without error" and it goes into an infinite switch between those two messages. Other than that it works awesome.