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)? 开发者_如何学JAVAI'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".
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
It should be possible to use DBEngine errors: http://msdn.microsoft.com/en-us/library/bb177491(office.12).aspx
精彩评论