• Wasting Time

    Author
    Topic
    #471321

    I’ve got several users updating the same database at the same time using a loop in a DAO recordset. Occassionally I hit a record that is locked but a second later it will process OK.

    What is the best way to waste a second before I retry.

    Code:
            intRetries = 0
        strAction = "Update Server Record"
        Set rstServer = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
        ' *** for each record add or update ***
        If rstServer.EOF Then
            rstServer.AddNew
        Else
    Retry_Edit:
            rstServer.Edit
        End If
        For Each fld In rstLocal.Fields
            strFieldName = fld.Name
            rstServer(strFieldName) = rstLocal(strFieldName)
        Next
        rstServer.Update
    Exit_Procedure:
        On Error Resume Next
        Exit Function
    Error_Handler:
        Select Case strAction
            Case "Update Server Record"
                If intRetries < 5 Then
                    intRetries = intRetries + 1
                    'waste some time
                    Resume Retry_Edit
                end if
            Case Else
                    
        End Select
        MsgBox "Error: " & Err.Number & "; Description: " & Err.Description, vbCritical, DBEngine(0)(0).Properties("AppTitle")
        Resume Exit_Procedure
        Resume  'used for debugging
    Viewing 2 reply threads
    Author
    Replies
    • #1241508

      The two options that come to mind are DoEvents or Sleep.

      You can read about them here

    • #1241509

      You could use a Timed Do Loop to put a pause in for a specific duration

      eg

      Code:
      'Define at the Start
      Dim dteTime As Date, dteWait As Date
      
      dteWait = TimeValue("00:00:05")    'Is 5 Seconds
      
      'Then Put in a Timed Pause
      dteTime = Time
      Do Until Time >= dteTime + dteWait
      Loop
      
      'Continue Code
      
      
    • #1241513

      Nice, thanks John and Andrew. and Luke

    Viewing 2 reply threads
    Reply To: Wasting Time

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: