Error Handling Query Errors

[bs0d]

I'm not real polished on error handling, and I'd like someone to sanity check my function. Here's what is intended:

run 3 queries (insert, delete, insert)
capture rows affected
store error numbers and descriptions (if any)
provide update status in update log table

Public Function myUpdate()

Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim lngPropertiesAdded As Long
Dim lngRowsAdded As Long
Dim lngRowsDeleted As Long
Dim lngNewRows As Long
Dim ErrLoop As Error
Dim StrErrorNum As String
Dim StrErrorDesc As String

On Error GoTo Error_Handler

'Add properties:
dbs.Execute "__Append_tblProperties", dbFailOnError
lngPropertiesAdded = dbs.RecordsAffected

'Delete previous X:
dbs.Execute "__Delete_Changed_Dates", dbFailOnError
lngRowsDeleted = dbs.RecordsAffected

'Append all daily data:
dbs.Execute "__Append_data", dbFailOnError
lngRowsAdded = dbs.RecordsAffected

lngNewRows = lngRowsAdded - lngRowsDeleted

'Record update:
dbs.Execute "INSERT INTO tblTaskUpdateLog ( runDate, PropertiesAdded, RowsDeleted, RowsAdded, NewRows, Errors, ErrorDescription )" & _
"VALUES (Now(), " & lngPropertiesAdded & ", " & lngRowsDeleted & ", " & lngRowsAdded & ", " & lngNewRows & ", " & StrErrorNum & ", '" & StrErrorDesc & "');", dbFailOnError

Set dbs = Nothing
Application.Quit

Error_Handler:
If DBEngine.Errors.Count > 0 Then
    For Each ErrLoop In DBEngine.Errors
        StrErrorNum = ErrLoop.Number & ", "
        StrErrorDesc = ErrLoop.Description & ", "
    Next ErrLoop
End If
Resume Next
    
End Function

For Access 2013, I assume an error in the query terminates the query execution and no records will be changed, is this correct?
Will Resume Next in the handler attempt each query regardless of possible error of previous? Thanks for any feedback

 

[Micron]

For Access 2013, I assume an error in the query terminates the query execution and no records will be changed, is this correct?

If you use the parameter dbFailOnError, changes are rolled back: https://msdn.microsoft.com/en-us/library/bb243015(v=office.12).aspx
Note that it is best to nest the execution in a BeginTrans block.

Will Resume Next in the handler attempt each query regardless of possible error of previous?

The resume next statement will likely throw errors when you try to assign the .recordsaffected value in each execution block. Probably one for a property that doesn't exist and one for trying to assign Null to a Long . I would prevent the assignment:
If DBEngine.Errors.Count = 0 Then lngRowsDeleted = dbs.RecordsAffected
Also, not convinced there would be anything to loop through in the error handler. As soon as one error is generated, execution should go there, so why would the count be anything but 1?

 

[bs0d]

I'm really trying to wrap my mind around error handling. Maybe this isn't even the right setup for managing errors in this function!

The resume next statement will likely throw errors when you try to assign the .recordsaffected value in each execution block. Probably one for a property that doesn't exist and one for trying to assign Null to a Long . I would prevent the assignment:
If DBEngine.Errors.Count = 0 Then lngRowsDeleted = dbs.RecordsAffected

That's a good point.

Also, not convinced there would be anything to loop through in the error handler. As soon as one error is generated, execution should go there, so why would the count be anything but 1?

That makes sense. I wanted to allow it to proceed if there was an error, but summarize any encountered. Maybe I can actually move that condition and loop outside of the error handler?

It also looks like I should have an "Exit Function" just above Error_Handler: to prevent error handler code from running without an error.

 

[Micron]

I don't see a problem with the error handling beyond what I mentioned (which is easy to 'fix')...

It also looks like I should have an "Exit Function" just above Error_Handler: to prevent error handler code from running without an error.

except possibly for the fact that if an error does occur, you quit the application and have no indication that something went wrong. The only way you'd know is if you examined the tables (which you can't do if they're in this db because you just closed it).

That makes sense. I wanted to allow it to proceed if there was an error, but summarize any encountered. Maybe I can actually move that condition and loop outside of the error handler?

I don't see a need to move it. Just remove the For and Next lines, unless you can envision a situation where the count would be > 0 when execution causes an error. For some situations, it could happen, I suppose, but not for this one as far as I can see. To be honest, I've never used the errors collection object of the db engine. I'm only saying that given what you are trying to do, I don't foresee more than one error, but hey, I've been wrong before.

What I do wonder about is other possibilities, not knowing exactly what's going on behind the scenes. For example, if no recs are created but are deleted, lngNewRows is negative. Also, if the .RecordsAffected variable assignment is bypassed due to the IF statement the variable should be 0. When you look at the table, you won't know if that 0 is because of an error or not. You might want to assign a numeric flag such as 999999, presuming that text would not be a viable data type for these fields. e.g.

If DBEngine.Errors.Count = 0 Then 
  lngRowsDeleted = dbs.RecordsAffected
Else
  lngRowsDeleted = 999999
End If

 

[bs0d]

Here is my updated code:

Dim dbs As DAO.Database
Set dbs = CurrentDb

Dim wrk As DAO.Workspace
Set wrk = DBEngine(0)

Dim lngPropertiesAdded As Long
Dim lngRowsAdded As Long
Dim lngRowsDeleted As Long
Dim lngNewRows As Long
Dim ErrLoop As Error
Dim StrErrorNum As String
Dim StrErrorDesc As String

On Error GoTo Error_Handler

'Begin transaction:

wrk.BeginTrans

'Add property:
dbs.Execute "__Append_tblProperties", dbFailOnError
    If DBEngine.Errors.Count = 0 Then
        lngPropertiesAdded = dbs.RecordsAffected
    Else
        lngPropertiesAdded = 99999
    End If

'Delete previous dates:
dbs.Execute "__Delete_Changed_Dates", dbFailOnError
    If DBEngine.Errors.Count = 0 Then
        lngRowsDeleted = dbs.RecordsAffected
    Else
        lngRowsDeleted = 99999
    End If

'Append all daily:
dbs.Execute "__Append_data", dbFailOnError
    If DBEngine.Errors.Count = 0 Then
        lngRowsAdded = dbs.RecordsAffected
    Else
        lngRowsAdded = 99999
    End If

lngNewRows = lngRowsAdded - lngRowsDeleted

'Record update:
dbs.Execute "INSERT INTO tblTaskUpdateLog (runDate, PropertiesAdded, RowsDeleted, RowsAdded, NewRows, Errors, ErrorDescription)" & _
"VALUES (Now(), " & lngPropertiesAdded & ", " & lngRowsDeleted & ", " & lngRowsAdded & ", " & lngNewRows & ", '" & StrErrorNum & "', '" & StrErrorDesc & "');", dbFailOnError

'Commit the transaction
wrk.CommitTrans dbForceOSFlush

trans_Exit:
    Set dbs = Nothing
    wrk.Close
    Application.Quit

    Exit Function 

Error_Handler:
If DBEngine.Errors.Count > 0 Then
        StrErrorNum = ErrLoop.Number & ", "
        StrErrorDesc = ErrLoop.Description & ", "
End If

'Rollback transaction
wrk.Rollback
Resume trans_Exit
    
End Function

except possibly for the fact that if an error does occur, you quit the application and have no indication that something went wrong. The only way you'd know is if you examined the tables (which you can't do if they're in this db because you just closed it).

So are you saying I should not have "Exit Function" above "Error_Handler:" ?

 

[xenou]

Are you expecting errors?

 

[bs0d]

Are you expecting errors?

No, in a perfect world, no errors happen. I'm trying to now model the ability to process errors should they ever unexpectedly present themselves.

 

[Micron]

So are you saying I should not have "Exit Function" above "Error_Handler:" ?

No, however I envision other issues with what you have. Here's my take, along with embedded comments and suggestions. Not that you have to do as I wrt strSql or Dim statements. NOTE:
This Dim StrErrorNum As String, StrErrorDesc As String, strSql as String is not the same as this
Dim StrErrorNum As String, StrErrorDesc, strSql Undefined variables are Variants.

Function Name????
Dim dbs As DAO.Database
Dim wrk As DAO.Workspace
Dim lngPropertiesAdded As Long, lngRowsAdded As Long
Dim lngRowsDeleted As Long, lngNewRows As Long
Dim ErrLoop As Error
Dim StrErrorNum As String, StrErrorDesc As String, strSql as String

Set dbs = CurrentDb
Set wrk = DBEngine(0)
'this style allows me to check sql in immediate window for errors in concatenation via debug
strSql = "INSERT INTO tblTaskUpdateLog (runDate, PropertiesAdded, RowsDeleted, RowsAdded, "
strSql = strSql & "NewRows, Errors, ErrorDescription) "VALUES (Now(), " & lngPropertiesAdded & ", "
strSql = strSql &  lngRowsDeleted & ", " & lngRowsAdded & ", " & lngNewRows & ", '" 
strSql = strSql & StrErrorNum & "', '" & StrErrorDesc & "');"
'Debug.Print strSql

On Error GoTo Error_Handler

'Begin transaction:
wrk.BeginTrans

'Add property:
dbs.Execute "__Append_tblProperties", dbFailOnError
'if directed to any of these error checks via err handler, assignment will be 99999
If DBEngine.Errors.Count = 0 Then
    lngPropertiesAdded = dbs.RecordsAffected
Else
    lngPropertiesAdded = 99999
End If

'Delete previous dates:
dbs.Execute "__Delete_Changed_Dates", dbFailOnError
If DBEngine.Errors.Count = 0 Then
    lngRowsDeleted = dbs.RecordsAffected
Else
    lngRowsDeleted = 99999
End If

'Append all daily:
dbs.Execute "__Append_data", dbFailOnError
If DBEngine.Errors.Count = 0 Then
    lngRowsAdded = dbs.RecordsAffected
Else
    lngRowsAdded = 99999
End If

lngNewRows = lngRowsAdded - lngRowsDeleted

'Record update:
dbs.Execute strSql, dbFailOnError
'if this one fails, err handler will try to commit trans because
'of the Resume Next. Maybe test recordsAffected or err count 
'& if zero, exit or quit app as you prefer

'Commit the transaction
wrk.CommitTrans dbForceOSFlush 
'**************
'the way you have it, ALL will be rolled back if ONE fails. If not OK, each
'Execute block needs to be wrapped in a transaction and rolled back if you wish.
trans_Exit:
    Set dbs = Nothing
    wrk.Close
    Set wrk = Nothing 'else you may leave it in pc memory
    Application.Quit
    Exit Function 
'***************
Error_Handler:
If DBEngine.Errors.Count > 0 Then
    StrErrorNum = ErrLoop.Number & ", "
    StrErrorDesc = ErrLoop.Description & ", "
    Resume Next 'else the 99999 assignment does not take place. Must go back to line after dbs.Execute
End If
'*******
'because I added Resume Next, this should never get executed. It needs to move, but where?
'In each execute block or roll back all transactions? If in their own, trans_Exit needs to clean up
'at the end.

'Rollback transaction
wrk.Rollback
Resume trans_Exit
'*******    
End Function

 

[xenou]

Unlike vba, you don't have to worry about errors quite so much. Now ... to await for Micron's rebuttal.

 

[james_lankford]

Unlike vba, you don't have to worry about errors quite so much. Now ... to await for Micron's rebuttal.

“We don't make mistakes, just happy little accidents.”
― Bob Ross

how about a rebuttal from Bob Ross ?

https://www.goodreads.com/author/quotes/102372.Bob_Ross