Error Handling with ADO.Execute

[Murray Bryant]

I have a problem with error trapping

I have been placed in the situation where I am unable to utilise the onerror
goto commands as i am writing some forms (access 2002) which get imported
into another access derived product in which the developers (in there
wisdom...), have decided to catch all of the errors in there own code
bypassing any i might write. Effectivly turning there product into a giant
black hole suppressing all error messages and not allowing my code to
handle.

Unfortunately our staff make extensive use of this product and also require
the addon's i have written. The main show stopper is where i was using
docmd.runsql to execute queries and then catching the errors

So I am invesitagating the posiblility of being able to pass data to the
database and catch the errors into a variable without the error being passed
to the access error handler.

Set conn = CurrentProject.Connection

' send the first query
strSQL = "INSERT INTO tblDHSamp SELECT tmppreassayimport.* FROM
tmppreassayimport; "

conn.Execute strSQL, records

When the conn.execute is called is it possible to suppress the error and
then access it?


Or does someone else have another suggestion.

Thanks

 

[Dan Artuso]

Hi,
If I understand you correctly, you could use the On Error Resume Next statement
and then check the Err object immediately after any line you think might generate
an error.

 

[Graham R Seach]

Murray,

If you declare the ADO Connection object using WithEvents, there are several
events that may be of interest:
* ExecuteComplete
* InfoMessage
* WillExecute
* CommitTransComplete
* RollbackTransComplete

Also, the Connection.Execute method returns a RecordsAffected property (a
Long Integer), which you can check.
Dim RA As Long
cn.Execute sSQL, RA

Finally, the Err object only returns VBA errors, not data access errors. To
check for data access errors in ADO, you should check the Connection
object's Errors collection, keeping in mind that the provider can return
multiple errors, so make sure to check the Count property.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[¹Ú¹®¼ö]

I have a problem with error trapping

I have been placed in the situation where I am unable to utilise the onerror
goto commands as i am writing some forms (access 2002) which get imported
into another access derived product in which the developers (in there
wisdom...), have decided to catch all of the errors in there own code
bypassing any i might write. Effectivly turning there product into a giant
black hole suppressing all error messages and not allowing my code to
handle.

Unfortunately our staff make extensive use of this product and also require
the addon's i have written. The main show stopper is where i was using
docmd.runsql to execute queries and then catching the errors

So I am invesitagating the posiblility of being able to pass data to the
database and catch the errors into a variable without the error being passed
to the access error handler.

Set conn = CurrentProject.Connection

' send the first query
strSQL = "INSERT INTO tblDHSamp SELECT tmppreassayimport.* FROM
tmppreassayimport; "

conn.Execute strSQL, records

When the conn.execute is called is it possible to suppress the error and
then access it?


Or does someone else have another suggestion.

Thanks