ADO Fail on error

[Mark Sreeves]

with DOA
sorry, DAO, you can do

db.Execute strSQL, dbFailOnError

to return errors from the database engine and trap in the vb code.

how do I do the same with ADO?

cnn.Execute strSQL ??????

[Ianpbaker]

The only way I have been able to do it is to enclose the .Execute in the standard On error goto statement. The downside to this is the Case statement to trap all the error codes And Display Your error message. Below Is Some Code to trap the system Messages. Hope It Will Help


Dim cnn1 As ADODB.Connection
Dim errLoop As ADODB.Error
Dim strError As String
Dim strSQL As String

On Error GoTo ErrorHandler

' Intentionally trigger an error.
Set cnn1 = New ADODB.Connection
cnn1.Open "test.mdb"
strSql = "Some insert statement
cnn1.Execute StrSql

Exit Sub

ErrorHandler:

' Enumerate Errors collection and display
' properties of each Error object.
For Each errLoop In cnn1.Errors
strError = "Error #" & errLoop.Number & vbCr & _
" " & errLoop.Description & vbCr & _
" (Source: " & errLoop.Source & ")" & vbCr & _
" (SQL State: " & errLoop.SQLState & ")" & vbCr & _
" (NativeError: " & errLoop.NativeError & ")" & vbCr
If errLoop.HelpFile = "" Then
strError = strError & _
" No Help file available" & _
vbCr & vbCr
Else
strError = strError & _
" (HelpFile: " & errLoop.HelpFile & ")" & vbCr & _
" (HelpContext: " & errLoop.HelpContext & ")" & _
vbCr & vbCr
End If


Debug.Print strError
Next

Resume Next

End Sub


[Edited by Ianpbaker on 04-17-2000 at 01:06 PM]

[Mark Sreeves]

Hmm...
thanks for your help that handles erroes due to incorrect SQL syntax
but if the UPDATE/INSERT fails because the database is readonly or open in design mode in Access no error is returned so it cannot be passed onto the Error Handler.

How can I get it to return an error in these instances?

[Ianpbaker]

Hi mark. I've been Searching through the microsoft sites and they do not have any information on Your problem which seems strange because I would of thought it was a common One. I'll keep looking and try to find the awnser.

Ian