Err; Errors; and DBEngine.Errors

[Guest]

I'm a little confused regarding the following:

DBEngine.Errors.Count : for which you cannot return a description
Errors.Count : for which you cannot return a description
Err : which seems more robust, but is this only for VBA errors?

Is there a way to separately handle: DAO errors; ADO errors; and VBA errors?

My thought is that in some cases there could be multiple errors encountered.
So I would like to be able to have a generic error function that is called
(using On Error Goto...) to process those errors I do not specifically trap.
This generic error function should:

1) Loop thru and display each DAO error in a message, then clear all DAO
errors; if no DAO errors were encountered, then skip to #2 below:

2) Loop thru and display all ADO errors in a message, then clear all ADO
errors; if no ADO errors were encountered, then skip to #3 below:

3) Loop thru and display all VBA errors in a message, then clear all VBA
errors; if no VBA errors were encountered, then go on to any other type of
errors (are there any?) and quit.

Can someone please post example code that would accomplish this? Or point me
to a web site that does a good job explaining it and showing examples?

Thanks.

 

[Douglas J. Steele]

The DAO Errors collection of the DBEngine object contains Error objects,
which have the following properties: Description, HelpContext, HelpFile,
Number and Source. You'd do something like:

Dim intLoop As Integer
Dim strError As String

If DBEngine.Errors.Count > 0 Then
For intLoop = 0 To (DBEngine.Errors.Count - 1)
With DBEngine.Errors(intLoop)
strError = strError & "DAO Error " & _
.Number & ": " & _
.Description & vbCrLf
End With
Next intLoop
End If

or

Dim errObject As DAO.Error
Dim strError As String

If DBEngine.Errors.Count > 0 Then
For Each errObject In DBEngine.Errors
strError = strError & "DAO Error " & _
errObject.Number & ": " & _
errObject.Description & vbCrLf
Next errObject
End If


The ADO Errors collection belongs to the Connection object. The ADO Error
object has the following properties: Description, HelpContext, HelpFile,
Number, Source and SQLState. As with the DAO Errors collection, you'd have
to loop through the collection (if populated), gathering details of each
Error in the collection. However, since you can be using more than one
Connection object in your application, there's no generic approach that I
can think of to doing this: your error handler would need to know the
specific Connection objects. Once you know the Connection, your code would
be something like:

Dim intLoop As Integer
Dim strError As String

If MyConnection.Errors.Count > 0 Then
For intLoop = 0 To (DBEngine.Errors.Count - 1)
With MyConnection.Errors(intLoop)
strError = strError & "ADO Error " & _
.Number & ": " & _
.Description & vbCrLf
End With
Next intLoop
End If

or

Dim errObject As ADO.Error
Dim strError As String

If MyConnection.Errors.Count > 0 Then
For Each errObject In MyConnection.Errors
strError = strError & "ADO Error " & _
errObject.Number & ": " & _
errObject.Description & vbCrLf
Next errObject
End If

As far as I'm aware, you'll only have a single Err object in VBA. If
multiple errors occur, I believe you only get the last one. So all you have
to do is see whether there is an error:

If Err.Number <> 0 Then
strError = strError & "VBA Error " & _
Err.Number & ": " & _
Err.Description
End If

Once all that code's run, check whether there's anything in strError, and
pop up a message if there is:

If Len(strError) > 0 Then
MsgBox strError, vbOkOnly Or vbCritical
End If

 

[Guest]

Just what I needed, thanks so much !!!

The DAO Errors collection of the DBEngine object contains Error objects,
which have the following properties: Description, HelpContext, HelpFile,
Number and Source. You'd do something like:

Dim intLoop As Integer
Dim strError As String

If DBEngine.Errors.Count > 0 Then
For intLoop = 0 To (DBEngine.Errors.Count - 1)
With DBEngine.Errors(intLoop)
strError = strError & "DAO Error " & _
.Number & ": " & _
.Description & vbCrLf
End With
Next intLoop
End If

or

Dim errObject As DAO.Error
Dim strError As String

If DBEngine.Errors.Count > 0 Then
For Each errObject In DBEngine.Errors
strError = strError & "DAO Error " & _
errObject.Number & ": " & _
errObject.Description & vbCrLf
Next errObject
End If


The ADO Errors collection belongs to the Connection object. The ADO Error
object has the following properties: Description, HelpContext, HelpFile,
Number, Source and SQLState. As with the DAO Errors collection, you'd have
to loop through the collection (if populated), gathering details of each
Error in the collection. However, since you can be using more than one
Connection object in your application, there's no generic approach that I
can think of to doing this: your error handler would need to know the
specific Connection objects. Once you know the Connection, your code would
be something like:

Dim intLoop As Integer
Dim strError As String

If MyConnection.Errors.Count > 0 Then
For intLoop = 0 To (DBEngine.Errors.Count - 1)
With MyConnection.Errors(intLoop)
strError = strError & "ADO Error " & _
.Number & ": " & _
.Description & vbCrLf
End With
Next intLoop
End If

or

Dim errObject As ADO.Error
Dim strError As String

If MyConnection.Errors.Count > 0 Then
For Each errObject In MyConnection.Errors
strError = strError & "ADO Error " & _
errObject.Number & ": " & _
errObject.Description & vbCrLf
Next errObject
End If

As far as I'm aware, you'll only have a single Err object in VBA. If
multiple errors occur, I believe you only get the last one. So all you have
to do is see whether there is an error:

If Err.Number <> 0 Then
strError = strError & "VBA Error " & _
Err.Number & ": " & _
Err.Description
End If

Once all that code's run, check whether there's anything in strError, and
pop up a message if there is:

If Len(strError) > 0 Then
MsgBox strError, vbOkOnly Or vbCritical
End If