Error handling - goto continue code upon certain error number

Karen Schaefer
Karen Schaefer used Ask the Experts™
on
Need help with the following code I need to continue the code after the error handling, what am I missing?

If err.number = 3156 then do that code and on completion of that code return to the rest of the code to continue the do loop statement.

K
Function DeleteData()
Dim strSql, strSql1 As String
Dim nName As String
Dim rs, rs1 As DAO.Recordset
       
   On Error GoTo DeleteData_Error

strSql = "SELECT Name" & _
        " FROM MSysObjects" & _
        " WHERE (Type=4) and (ID <> 2558) and (ID <> 970) and (ID <> 2554)"

Set rs = CurrentDb.OpenRecordset(strSql)
    rs.MoveFirst
    Do Until rs.EOF
        nName = rs.Fields("Name")
        If nName = "ACTION_TABLE" Or nName = "ACTIVE_AIRPLANES" Or nName = "TBLPROCESSINGLOG" Then
            GoTo cont:
        Else
            strSql1 = "Delete * from " & nName & ""
            CurrentDb.Execute (strSql1)
        End If

cont:

    rs.MoveNext
    Loop

   On Error GoTo 0
   Exit Sub

DeleteData_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DeleteData of Module modFunctions"
     If Err.Number = 3156 Then
        DoCmd.OpenTable nName, acViewNormal, acEdit
        DoCmd.RunCommand acCmdSelectAllRecords
        DoCmd.RunCommand acCmdDeleteRecord
        DoCmd.Close acTable, nName, acSaveYes
        Err.Number = 0
     End If

  

End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I think you want the Resume Next statement after your error handling. That should return control to the statement following the one which generated the error.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
You need to enumerate the errors collection.  Take a look at this:

http://support.microsoft.com/kb/209855

Jim.
Karen SchaeferBI ANALYST

Author

Commented:
Not sure Jim how to incorporate the ODBC Error handling into my code - Since I am trying to delete data from a ODBC linked Oracle table.  I have run into a couple of issue and Error 3156 was the first I identified.  Will  the code you recommended work on all error issues?

Karen
Aaron TomoskyDirector of Solutions Consulting

Commented:
Why not just run that code all the time? It's just deleting all records from a temp table right?
Karen SchaeferBI ANALYST

Author

Commented:
no not a temp table - they are linked Oracle tables - I need to make sure they are all empty prior to appending the new data.

K
Aaron TomoskyDirector of Solutions Consulting

Commented:
Right, same difference. So before appending data, can't you just always clear the tables?
Karen SchaeferBI ANALYST

Author

Commented:
that is what this code is for.  the problem I am having is since they are ODBC connected I am running into some issues - not always the same issue when deleting the data.  

Keep in mind this is for my testing phase - once I get it into production the deletion will happen on the Oracle side of things.

K
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
Then you'll have to account for all those "issues", either in your error handling section or directly in code.

What sort of "issues" are you running into? The code you have - deleting the data from the table - is fairly straight forward, although I would STRONGLY encourage you to avoid using GoTo, except for error handling. There is no need for your "GoTo cont:" statement - just add the code to MoveNext to the IF portion of the If - End If block.

Are ALL Of the tables linked Oracle tables? If so, you should use syntax like DELETE FROM MyTable, and not DELETE *. See this article:

http://www.dba-oracle.com/t_delete_statement.htm
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
Karen,

<<Not sure Jim how to incorporate the ODBC Error handling into my code - Since I am trying to delete data from a ODBC linked Oracle table.  I have run into a couple of issue and Error 3156 was the first I identified.  Will  the code you recommended work on all error issues?>>

  What most don't realize is that when you get an error, their is a collection behind that (there may be multiple errors).  Error 3156 is the first error that the ODBC driver rasies.  It then rasies other errors and fills the collection.

  When working in VBA with ODBC, if you only look at the first error, all you'll see is the 3156.  That doesn't give you anything specific to go on.  So you need to modify your error trapping code to this:

DeleteData_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DeleteData of Module modFunctions"
     If Err.Number = 3156 Then
         Dim errX As DAO.Error

        If Errors.Count > 1 Then
        For Each errX In DAO.Errors
         Debug.Print "ODBC Error"
         Debug.Print errX.Number
         Debug.Print errX.Description
      Next errX
   Else
      Debug.Print "VBA Error"
      Debug.Print Err.Number
      Debug.Print Err.Description
   End If
   Resume Exit_function

End Sub

  However after looking at your code again and the others comments, it appears I missed the point of your question.

  To me, generic error handing code at the end of a procedure should be for the unexpected errors.   Some include more specific cases.  For example, you issue a DoCmd.OpenReport and the user cancels, which will raise a 2156 error, so you'll see something like this:

DeleteData_Error:
   Select Case Err.Number

   Case 2156
       Resume Next

  Case Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DeleteData of Module modFunctions"
     If Err.Number = 3156 Then
         Dim errX As DAO.Error

        If Errors.Count > 1 Then
        For Each errX In DAO.Errors
         Debug.Print "ODBC Error"
         Debug.Print errX.Number
         Debug.Print errX.Description
      Next errX
   Else
      Debug.Print "VBA Error"
      Debug.Print Err.Number
      Debug.Print Err.Description
   End If
   Resume Exit_function

   End Select

End Sub


  The problem with that is that in many cases, you may be running multiple reports (or operations) in the same code you might want different outcomes.  For example, a user canceling a delete log report is not allowed, so doing a "resume next" after that and continuing execution is the wrong thing to do. So you have two choices:

1. Use multiple error handlers:

   On Error Goto DeleteData_Error
   ' do some stuff

   ' Need to run the report
   On Error Goto Delete_Data_Report_Error
   DoCmd.OpenReport "DeleteReport"

   ' Switch back to the other error handler
   On Error Goto DeleteData_Error

   ' Continue doing more stuff.

Delete_Data_Exit:

  Exit Sub  



Delete_Data_Report_Error:
    Select Case err.number

    Case 2156
          MsgBox "You can't cancel the delete report"
          Resume

    End Select

DeleteData_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DeleteData of Module modFunctions"
     If Err.Number = 3156 Then
         Dim errX As DAO.Error

        If Errors.Count > 1 Then
        For Each errX In DAO.Errors
         Debug.Print "ODBC Error"
         Debug.Print errX.Number
         Debug.Print errX.Description
      Next errX
   Else
      Debug.Print "VBA Error"
      Debug.Print Err.Number
      Debug.Print Err.Description
   End If
   Resume Delete_Data_Exit

2. In-line error handling, which unfortunately VBA is not great at.

  Looks like this:

  On Error Goto 0
  Err = 0
  ' Attempt Delete of records
  CurrentDB().Execute "DELETE * FROM <table>", dbFailOnError
  If Err<>0 then
     ' What the heck happened? Do we need to do anything?
  End If

  ' Now do the insert.

  Those are the two approaches.  So in summary when working in a procedure, if you want to have different outcomes for the same error (exit procedure or carry on), then you must use multiple error handlers or use in-line error handling.

  Sorry for the examples above; they are not the best, but I am short on time at the moment.

Jim.


Karen SchaeferBI ANALYST

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial