Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Posts
    13

    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
    ' created.
    Set db = CurrentDb()

    ' Create a temporary QueryDef object to retrieve
    ' data from the database.
    Set qdfFindNextID = db.CreateQueryDef("")

    With qdfFindNextID
    .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

    End With

    ' 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
    With rstNextID
    .MoveFirst
    lngNextID = CLng(!NextID)

    .Edit ' first select edit mode
    !NextID = CLng(!NextID + 1) ' make changes
    .Update ' commit changes in buffer to disk
    End With

    rstNextID.Close ' unlocks here
    db.Close

    GetNextID = lngNextID ' assign next value to function's return value

    Exit Function

    ErrorHandler:
    ' Display error information.
    MsgBox "(From " & Err.Source & ") Error number " & Err.Number & ": " & Err.Description
    ' Resume with statement following occurrence of error.
    Resume Next

    End Function


    ===========
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2001
    Posts
    336
    Hi,

    Here is some code I use to trap lock errors:

    *----------------
    ProcEnd:
    Exit Sub

    ProcErr:
    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
    DAO.DBEngine.Idle
    For nYY = 0 To 1000
    DoEvents
    Next
    Resume
    Else
    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"
    Resume
    Else
    UpdateLog "Routine Name: Lock error: " & Err.Number & " Resume Next"
    Resume Next
    End If
    End If

    Case Else
    UpdateLog "Routine Name: Error# " & Err.Number & ": " & Err.Description
    MsgBox "Error# " & Err.Number & ": " & Err.Description, vbCritical + vbOKOnly, "Error"
    Resume ProcEnd

    End Select
    *-------------

    UpdateLog - is a subroutine, which stores output in a Log table.


    HTH,

    Igor

  3. #3
    Join Date
    Jun 2002
    Posts
    13
    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?

    Jeremy

  4. #4
    Join Date
    Jun 2002
    Posts
    13
    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.

    Jeremy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •