Message reason why Execute method failed

Question

I use the DAO method Execute to delete some records. If this fails this is clear by checking RecordsAffected (it will be 0). But is it possible to get the error message (for instance, to log or to show to the user)? I've try to delete the records by hand in the Table grid I get a clear dialog message, e.g. "The record cannot be deleted or changed because tabel x includes related records".

Answer 1

It should be possible to use DBEngine errors: http://msdn.microsoft.com/en-us/library/bb177491(office.12).aspx

Answer 2

Include the dbFailOnError option with the Execute method to capture your DELETE errors. Without dbFailOnError, your DELETE can fail silently.

Relying on RecordsAffected to indicate a DELETE failure can be misleading. For example if your DELETE includes "WHERE Sample=5", and there is no row with a Sample value of 5, RecordsAffected will be 0. That is not an error to the database engine.

In the following sample, the DELETE fails because there is a relationship, with referential integrity enforced, between tblParent and tblChild. So the message box says "The record cannot be deleted or changed because table 'tblChild' includes related records".

Public Sub DeleteFailure()
    Dim strSql As String
    Dim strMsg As String
    Dim db As DAO.Database

On Error GoTo ErrorHandler

    strSql = "DELETE FROM tblParent WHERE id = 1;"
    Set db = CurrentDb
    db.Execute strSql, dbFailOnError

ExitHere:
    On Error GoTo 0
    Debug.Print "RecordsAffected: " & db.RecordsAffected
    Set db = Nothing
    Exit Sub

ErrorHandler:
    strMsg = "Error " & Err.Number & " (" & Err.Description _
        & ") in procedure DeleteFailure"
    MsgBox strMsg
    GoTo ExitHere
End Sub

Update: Here is a revised ErrorHandler to accommodate multiple errors triggered by a DAO operation.

ErrorHandler:
    Dim errLoop As Error
    Debug.Print "Errors.Count: " & Errors.Count
    For Each errLoop In Errors
        With errLoop
            strMsg = "Error " & Err.Number & " (" & _
                Err.Description & _
                ") in procedure DeleteFailure"
        End With
        MsgBox strMsg
    Next
    Set errLoop = Nothing
    GoTo ExitHere