VBA If Error Try Again

Question

We have automated our reporting processes using SQL Server, Access and Excel. One of our queries however has difficulties running in the morning. Sometimes it gets a timeout error. When this happens, the whole system breaks down and we have to manually continue the processes.

I was hoping to add a VBA loop to allow the query to try again if it happens to fail.

I want the system to:

1. Run the query.
2. If it fails, wait 5 minutes and try again.
3. If it fails 5x in a row, stop the code.

I have written the following code but I have no way of testing it. I was hoping any of you guys could check it out and comment on wether it should work or not.

    'Counter for the errors
    ErrorCount = 0
    GoTo CheckConnection

CheckConnection:
    If ErrorCount < 6 Then
    'Try to execute the query
        db.Execute sqlq
        'If it fails, ignore the error message and go to BadConnection
        On Error GoTo BadConnection
    Else
    'If the query failed 5x, just give up and show the error message
        db.Execute sqlq
        Resume Next
        End If

BadConnection:
    'Add +1 to the counter
        ErrorCount = ErrorCount + 1
        'Allow the application to wait for 5 minutes
        Application.Wait (Now + TimeValue("0:05:00"))
        'Try the query again
        GoTo CheckConnection

Solution

You aren't resuming in the correct spot, it needs to be in the error-handling code:

    'Counter for the errors
    ErrorCount = 0
    GoTo CheckConnection 'This statement is pointless if the label is directly after it

CheckConnection:
    'Try to execute the query

    ' This says to go to BadConnection if an error occurs after it,
    ' not if an error occurred previously
    On Error GoTo BadConnection
    db.Execute sqlq
    ' Start allowing errors to crash Excel again
    On Error GoTo 0
    'Query worked - continue processing
    '....

    '...
    Exit Sub

'Error handling code    
BadConnection:
    ' Start allowing errors to crash Excel again
    On Error GoTo 0
    If ErrorCount = 5 Then
        'If the query failed 5x, just give up and show the error message
        MsgBox "Giving up"
        Exit Sub
    End If
    'Add +1 to the counter
    ErrorCount = ErrorCount + 1
    'Allow the application to wait for 5 minutes
    Application.Wait (Now + TimeValue("0:05:00"))
    'Try the query again by Resuming at CheckConnection
    Resume CheckConnection

Comments

[lanartri]

I wouldn't use recursion here. A simple wait and resume seem to me simpler.

[Vityata]

@PatrickHonorez - with wait & resume the code becomes a bit spaghetti to me. But if it is really a small piece of up to 100 lines anything is ok.

[TheNiers]

Thank you, I tested it with a basic divide by 0 error in Excel and it works like a charm. My VBA is not the strongest so this definitely helped.