VBA Error handling, identifying the point of failure

Just going with your constraints taken at face value, have you considered something like the following... I woudln't recommend this approach, but if nothing else about your situation can be changed, this might work for you... Considering the following requirements

• Single-line operations
• Detect failed line
• Be able to restart on-or-around failed line (+/- 1, etc).

Public Sub RunCode(Optional ByVal StartLine As Integer = 0)
  On Error GoTo Handle
  
  If StartLine > 0 Then
    If StartLine = 10 Then GoTo 10
    If StartLine = 20 Then GoTo 20
    If StartLine = 30 Then GoTo 30
    If StartLine = 40 Then GoTo 40
  End If

10:   Debug.Print "10"
20:   Debug.Print "20"
30:   Err.Raise -1, "Jones"
40:   Debug.Print "40"
  Exit Sub
  
Handle:
  Debug.Print "Handle [" & Erl & "]: " & Error

End Sub

The trick used is the Erl statement from VBA... It's generally not used, because it requires the explicit line numbers, seen above... But, since you ALREADY effectively numbered you lines, it would seem like a better option than what you're currently doing.

Second, if you NEED to restart AT or around the line that failed, you need a goto label anyway... That is, you would have to have a third line on each clause the way you're doing it, so you can do a GoTo to it. That is the only way to restart in the middle of your control structure, as you have it. By combining the line numbering as the labels, you effectively reduce this, hopefully making your life easier in the mid-term (not the long term, since numbering your lines is inherently problematic).

The Erl will give you the line that failed, which can be stored somewhere like either a return value or a Global (or wherever you can store and retrieve it). What you have left is the requirement to restart at the given line. Unfortunately, to the best of my knowledge, you can't simply do GoTo StartLine, as would be the easiest, so, as you see, each line is enumerated twice (but, you already had that, right?).. The function, via Optional parameter, can be effectively re-entered at any given point.

This, then, is the most straight-forward way to accomplish your three goals. With that said, you could consider some other options... For example, you could wrap your call to DoCmd.OpenQuery "...", acViewNormal, acEdit in some function, and attempt to apply some retry-logic on the same... That is,

Public Function RetryMyQuery(ByVal Query As String) As Boolean
Const MaxRetries As Long = 5
DoRetry:
  On Error Goto QueryFailed
  Dim FailureCount as Long
  DoCmd.OpenQuery Query, acViewNormal, acEdit
  RetryMyQuery = True
  Exit Function

QueryFailed:
  FailureCount = FailureCount + 1
  If FailureCount >= MaxRetries Then Exit Function
  If MsgBox("Query Failed [" & Query & "]: " & Err.Description, vbRetryCancel, "Query Failed") = vbRetry Then Resume DoRetry

End Function

Each line in the main function would simply call this function with the query name:

If Not RetryMyQuery("SP_export_sumary") Then MyErrorLine = 19

What this is doing is attempting to retry each operation up to 5 times (configurable), with a message box between each retry to force the user to interact. They can double-check the connection, re-open anything as needed, and try again, or, simply cancel the operation.

The stub function encapsulates the retries and returns True on success, so in the main loop, you can still detect the error, and even record the line that failed, if desired. However, again, if you NEED to re-enter the control logic at any given point, you likely need something like solution #1. Other alternatives to this would be to put the list of operations in some kind of String Array and loop through them, and then you're not re-entering hard-coded logic lines, you just start at an arbitrary point in the list. It depends on your needs.

Hopefully something in this would be of assistance. It is not always possible to change a lot of the control structures on existing code, and while I wouldn't ever set out and design something like the first example, if you merely need to modify what's there, a down-and-dirty way is sometimes the best. Just remember, maintaining line numbers is terrible. BASIC (Before VB or VBA, or even QuickBasic) had line numbers and always had this problem. They aren't used any more for a reason. It should be considered a last resort, but, if it gets things up and going, I've done worse.

NOTE: It may also be helpful to point out that GoTo Labels are only scoped to the current function. Pre-pending the name of the function (Export_to_sp_Err:) only results in more characters being typed, and never being able to remember what the handler is named by the time you get to the end of a long function. Just a tip!

The classic way of doing this is to avoid the very limited old scool way of dealing with errors and instead implement a Tryxxx function. The Role of the TryXXX function is to return True of False to indicate the success or fail of an action. One of the parameters (usually the last) is declared as ByRef so that the result of the action can be passed back via the parameter.

I'd also restructure how you provide the strings for the queries so that you separate them from the code that does the querying

XXXQuery is the name of the query for which you have the sequence of query statments.

SOmething along these lines

Public Sub DoXXXQuery(ByRef ipQuerySeq As Collection)

    Dim myItem As Variant
    For Each myItem In ipQuerySeq
    
        Dim myResult as Variant  'as an example
        If Not TryDoQuery(myItem, acviewnormal, acedit, myresult) Then
        
            MsgBox "Query execution stopped unexpectedly at '" & myItem & "'"
            'put code here for action after failure
        End If
        
    Next
    
End Sub

' ipViewNormal, ipEdit, and opResult are variants as the OP
' doesn't provide enough information to define more appropriately
Public Function TryDoQuery(ByVal ipCommand As String, ByVal ipViewNormal, ByVal ipEdit, ByVal opResult As Variant) As Boolean

    TryDoQuery = False
    
    On Error Resume Next
    DoCmd.OpenQuery ipCommand, ipViewNormal, ipEdit
    If Err.Number <> 0 Then
    
        Err.Clear
        Exit Function
        
    End If
    
    err.clear
    opResult = ????? ' its not clear what the result of the query is
    
    TryDoQuery = True
    
End Function

Public Function GetXXXQuerySeq() As Collection

    Dim myC As Collection
    Set myC = New Collection
    
    With myC
    
        .Add "6-delete_C", acviewnormal, acedit
        .Add "6-rename_B_to_C"
        .Add "6-rename_A_to_B"
        .Add "6-rename_Active_to_A"
        .Add "6- export_to_Sp"
        .Add "6- move_A_to_backup"
        .Add "6-delete_A"
        .Add "upload_size_tracker_to_SP_new"
        .Add "Clear_old_for_current_SP"
        .Add "3_open_op"
        .Add "export change log to new sp"
        .Add "clear_sp_table_size"
        .Add "count_local_std_res"
        .Add "count_local_res"
        .Add "count_sp_std_res"
        .Add "count_sp_ol_res"
        .Add "count_sp_backup_ol_result"
        .Add "count_audit_log"
        .Add "count_row_tracker"
        .Add "SP_export_sumary"

    
    End With
    
    Set GetXXXQuerySeq = myC
    
End Function