July 11, 2002 at 1:39 pm
I am executing a s.p. from VBA in an Access application using ADO. I would like to capture and display any SQL errors to the Access user. I got the following code from BOL:
On Error GoTo ErrorExecute
ErrorExecute:
' Enumerate Errors collection and display
' properties of each Error object.
For Each errLoop In cnn.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
I don't understand how to display the output to my Access user. I am familiar with Debug.Print using the immediate window when debugging. How do I get this to a VBA message box? What am I missing here?
Thanks!!!
July 11, 2002 at 2:25 pm
Once you've built the string should just be a simple call to msgbox, like this:
Msgbox strError
Andy
July 12, 2002 at 6:50 am
Boy do I feel dumb!! Thanks Andy.
If I may a few followup questions:
1. If in an Access VBA procedure a sp is executed with ADO in addition to normal Access activites (i.e. DoCmd.OpenForm etc.)which error handling takes precedent? I mean is the whole procedure wrapped in my normal VBA error handler but the execute sp part in the ADO error handler?
2. Is it better to split off the execute sp into its own procedure with ADO error handling called from the original procedure?
As you can tell I am struggling with the overall picture of ADO errors within VBA procedures.
Thanks for any help!!
July 12, 2002 at 7:43 am
No sweat, as long as you try we dont mind helping you find the answers if we can.
It'll branch to whatever error handler is active. Typically you might have code like this:
on error goto MainHandler
blah blah
time to do the proc (or whatever that might require a special handler
on error goto ProcHandler
run proc
then reset the handler (if you need to)
on error goto MainHandler
Also you don't necessarily need two handlers, if there are no errors in the ado errors collection it won't return anything or generate an error, so you could just include that if you wanted to.
Whether to split is more style than substance. I typically (in VB) like to put procs in separate subs, but sometimes that's overkill. In any case handling the error is never easy. Well, handling it well that is!
Andy
July 12, 2002 at 8:08 am
Thanks again Andy, I am getting there!
1. If while the sp is running (mine takes about 2 minutes) a SQL error occurs, will the message pop up real time? Does ADO wait till the sp is finished before the error collection is populated?
2. Should I have error handling within the sp to stop it if an error occurs?
3. How would I get out of the VBA procedure, so that code after the sp does not execute if an error occurs in sp?
July 12, 2002 at 8:23 am
Typically once an error occurs the proc will end. I prefer to write as little error handling as possible, instead coding aggressively to identify problems as they happen and degrade/react as necessary. May sound like error handling, but the difference is with an error handler you just code and if anything goes wrong, you drop to the handler. My coding to check for problems (rather than errors) you get stronger code. An example might be in a proc an early operation is to look up a key from a table. The select can run without error and still return a null. I'd check for that condition and depending on the important, might then insert the row, not execute some portion of the proc and return a return code or output param, raise an error, or just exit the proc. You can't trap for every thing that can go wrong, trap the ones that seem likely and let your error handler get the rest.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply