如果出现错误，请重试
如果出现错误，请重试
提问于 2017-08-22 09:22:13
我们已经使用Server、Access和Excel实现了报告过程的自动化。然而，我们的一个查询在上午运行时遇到了困难。有时会出现超时错误。当这种情况发生时，整个系统就会崩溃，我们必须手动继续这些进程。
我希望添加一个VBA循环，允许查询在发生故障时再试一次。
我希望这个系统：
- 运行查询。
- 如果失败，请等待5分钟，再试一次。
- 如果它连续5倍失败，请停止代码。
我编写了以下代码，但我无法测试它。我希望你们中的任何一个人都能检查一下，并评论它是否应该起作用。
'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回答 2
Stack Overflow用户
回答已采纳
发布于 2017-08-22 09:32:59
您没有在正确的位置恢复，它需要在错误处理代码中：
'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 CheckConnectionStack Overflow用户
发布于 2017-08-22 09:32:16
这是可以使用递归和可选参数解决的问题。
Option Explicit
Public Sub TestMe(Optional errorCount As Long = 0)
On Error GoTo TestMe_Error
'Your code just to test it, make an error
Debug.Print errorCount / 0
On Error GoTo 0
Exit Sub
TestMe_Error:
Debug.Print "Error " & Err.Number & " (" & Err.Description & ") in procedure TestMe"
errorCount = errorCount + 1
Select Case errorCount
Case 1, 2, 3
Application.Wait Now + #12:05:00 AM#
Call TestMe(errorCount)
Case Else 'The 5th time it exits
Exit Sub
End Select
End Sub使用递归重新运行代码。代码重新运行的时间保存在参数errorCount中。因此，它退出的时间到了。
通常，避免使用GoTo并仅用于错误处理。GOTO still considered harmful?
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/45813664
复制相关文章
相似问题
腾讯云开发者
