Annoying Error Handling Problems

[Guest]

When I'm coding a function in VBA, I might run into a situation where I need
to use "On Error" in situations where an error WILL most definitely pop up.
Take this code for example:

....
while rs.EOF = false
Dim accobj as Access.Application
set accobj = new Access.Application
accobj.OpenCurrentDatabase "c:\blah.mdb", true
on error goto AnErrorOccured
accobj.Run "SomeProcedure", rs.fields("DateToUse").Value
accobj.Quit acQuitSaveAll
AnErrorOccured:
Err.Clear
set accobj = nothing
rs.MoveNext
wend
rs.Close
....

In this case above, I am using VBA in Access to open up some other Access
database and run a procedure. This NORMALLY works well, but in my real life
version of what I'm running, that "SomeProcedure" first checks to see if the
DateToUse is a weekend, if it is then it simply calls Application.Quit
because it doesn't need to process on the weekends. If the code in
SomeProcedure causes the new Access instance to quit, an error occurs. Even
though there's error handling in place, if I'm using this code to iterate
through several dates and process the Database (and in my case, Compact
afterwards each time, but I didn't want to clutter the example), on the
second time it sees this error I will get a Debugger message regardless of
the fact that I've cleared Error on the handler.

Long story short, I don't want my program execution to be stopped by errors,
even if there is (and there most definitely IS in this case) errors that will
happen. Does anyone know of a workaround for this?

One solution I've come to before when trying to provide a workaround before
was to put anything that might be erroneous in its own procedure. It seems
that when you do that, VBA will run it correctly and the error will be
handled. But this is a drain when you're trying to keep track of several
local variables and don't want to have to write a proc to pass all of the
variables off that you need just to run one or two lines of code.

If anyone knows of a faster solution where I can clear the Error fully and
make sure VBA doesn't cause a debugger message the second time an error
occurs, even if handled, then please let me know.

 

[Guest]

Before running the code, add the line
On Error Resume next

that will cause the code to continue even if there is an error, and without
prompting the user with a message

On Error resume next
while rs.EOF = false
Dim accobj as Access.Application
set accobj = new Access.Application
accobj.OpenCurrentDatabase "c:\blah.mdb", true
on error goto AnErrorOccured
accobj.Run "SomeProcedure", rs.fields("DateToUse").Value
accobj.Quit acQuitSaveAll
set accobj = nothing
rs.MoveNext
wend
rs.Close

 

[John Nurick]

My first thought was to test the date first and only launch the other
database if you actually want to run "SomeProcedure" on that record.

The next is that SomeProcedure is very badly behaved: at weekends it
terminates by calling Application.Quit, on weekdays it terminates but
leaves its instance of Access running. Fix that and your problem goes
away.

Also, your error handler doesn't seem to do its job. The location of the
OnError Goto means that any time the error handler is invoked, there
will be an instance of Access with an open database in an unknown state.
I don't think you can rely on simply releasing the object variable to
tidy that up (unless, at least, you're totally confident of the error
handling in SomeProcedure).

Finally, you don't say what error message you get or what line of code
is highlighted when you do hit the debugger.

 

[Guest]

My first thought was to test the date first and only launch the other
database if you actually want to run "SomeProcedure" on that record.

Yes that thought occurred to me too, but what about holidays? (those don't
get reported either)

The next is that SomeProcedure is very badly behaved: at weekends it
terminates by calling Application.Quit, on weekdays it terminates but
leaves its instance of Access running. Fix that and your problem goes
away.

That's going to take some major rework (the proc that does the date checking
is nested within several subprocedures and returns a date value not a boolean
(b/c it has to return date-1 on the Tuesday-Thursday and date-2 on Monday).

Also, your error handler doesn't seem to do its job. The location of the
OnError Goto means that any time the error handler is invoked, there
will be an instance of Access with an open database in an unknown state.
I don't think you can rely on simply releasing the object variable to
tidy that up (unless, at least, you're totally confident of the error
handling in SomeProcedure).

I tried this and it seemed to work for this application (it hasn't worked
for me before in other instances):

....
on error goto FoundError
accobj.Run "SomeProcedure", rs.fields("DateToUse").Value
accobj.Quit acQuitSaveAll
goto Continuing
FoundError:
resume Continuing
Continuing:
....

Finally, you don't say what error message you get or what line of code
is highlighted when you do hit the debugger.

It simply said "Automation Error" when the invoked instance of Access had
quit, didn't give an Error Code.

 

[John Nurick]

I'd be inclined to think along these lines (air code)

Dim accobj As Access.Application


Do Until rs.EOF
On Error Resume Next
Set accobj = New Access.Application
Accobj.OpenCurrentDatabase "c:\blah.mdb", true
Accobj.Run "SomeProcedure" blah blah

At this point either SomeProcedure will have aborted because it's the
wrong day of the week, quitting accobj in the process - or it will have
completed its work and left accobj in memory.

If Not (accobj Is Nothing) Then
accobj.Quit acQuitSaveAll
Set accobj = Nothing
End If
rs.MoveNext
On Error Goto 0
Loop
rs.Close

 

[John Nurick]

Something I should have asked earlier: have you studied either of these
articles?

Using Microsoft Access as an Automation Server
http://support.microsoft.com/?id=147816 (Access 97)
http://support.microsoft.com/?id=210111 (Access 2000 and later)

 

[Guest]

Thank you John I'm printing that off for my own records as I speak.