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