Trapping System Errors in a Global Module (1 Viewer)

[nrgins]

The built-in Form_Error and Report_Error error handlers are great for trapping system errors in caused by form or report code.

But what about in a global module? Is there a way to trap system errors within a global module?

(And, just to be clear, I'm not talking about the standard On Error Goto errors, which are easily trapped. I'm referring to system errors which aren't trapped by On Error Goto.)

Thanks!

(Cross-refed here: https://www.utteraccess.com/topics/2065012)

 

[ebs17]

Ask Wayne Phillips:

vbWatchdog - Global Error Handler and Tracing utility

A fantastic global error handler for any VBA enabled application - such as Microsoft Access and Microsoft Excel.

www.everythingaccess.com

 

[The_Doc_Man]

Can you enumerate some of the errors that concern you?

First, I'm going to suggest that you look into a web search of "Windows Event Driven Programming" to get some overviews and an appreciation of how Windows wants/needs each program to work.

As long as you are stuck using VBA under Access, I'm almost certain that you cannot even SEE any errors that Access itself wouldn't trap. Considering that VBA is emulated code, not machine code, you have a problem with targeting the place to change things and another problem in establishing a usable target for event interception. Beyond that factor, we don't have any visibility into Access internal code (because it isn't an Open Source style of product), so I'm going to say "a qualified 'no'". Here's my reasoning.

Any Windows program has to establish an events handler. When Windows detects that a notification-level event has occurred, it triggers a call to the event handler deemed appropriate - which usually means the program that was running when the event trigger was detected. If you look at the Windows Event Log and find a program crash, you would see that it knows which program and which element within the program was active at that time, which is how it chooses the program to call.

These system-declared events trigger a call to the main program's currently declared handler with an error number as a parameter and you have to dispatch based on the error. For you to establish such a handler, you would have to find and usurp the extant Access handler because you can't have two "competing" handlers. If you did overlay the Access handler, it would stop working correctly for all other errors that occur during normal program flow because of event-oriented programming principles.

So... for your request, we decide that Access got called with an error. In that context, one of two things occurred. Access determines which of two possible conditions applies: EITHER an Access event was active at that time, which means the active error handler (On Error GoTo variety) must be called, or Access was running its own internal code, which means Access must call its own error handler. Let's say your code was active. So Access has fielded the error and decided you can have it. It finds the currently declared handler in VBA context and issues a call that passes in an error number to your declared handler. Good luck with handling it. If you can't handle it, the rules say you should either abort your code catastrophically or re-signal the error (which is in itself a form of minor catastrophe, because that code just died but gracefully.)

If Access DIDN'T call you, it is because the error class is one you can't handle. (Sudden image of Jack Nicholson yelling "You can't handle the trap.") The issue is that if your code wasn't active at the time, this condition probably isn't one that you could have handled anyway because it would be an arbitrary condition unrelated to your code. What are you going to do with that?

Earlier I said "a qualified NO" because of products like vbWatchDog. Eberhard has posted a link to that third-party error handler system, but I think you would find that it does no better at handling things outside of the things normally handled by a user-written error handler. I mean that in the sense that if you got an error that Access wouldn't normally pass to your handler, what are you going to do about it? If Access wouldn't have called you anyway, vbWatchdog would be unlikely to get called. That is because of the way that Windows dispatches errors. However, doing on-line research, it appears to use an add-in library that will add a layer to the normal Access trap-handler mechanism to stand between your code and the Access "Last-Chance" handler that gives you the dreaded "Debug" or "Abort" option. In that sense, it can do some of what you ask, which is to say visibility of errors that occur even when your own event code isn't running.

 

[nrgins]

Can you enumerate some of the errors that concern you?

Anything that happens in the system outside of VBA. The form and report On Error event procedures handle these.

In the past I've logged the types of errors that the On Error event traps. Some examples are:

• Your network access was interrupted. To continue, close the database, and then open it again.
• You can't save this record at this time.@ABCDatabase Database may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?@@20@@@2
• Unrecognized database format '|'.
• You cannot convert the open database to an MDE file by running a macro or Visual Basic code.@Instead of using a macro or code, on the Database Tools tab, in the Database Tools group, click Make MDE.@@1@@@1

etc.

I'd like to be able to trap them in a global module, but there doesn't seem to be a way to do it like there is with form or report modules.

 

[The_Doc_Man]

Typically, your error handlers would not be able to do anything with most of those anyway. The ability to see those errors has nothing to do with whether you could write a handler to fix those errors.

"Loss of network connection" and "Unrecognized database format" are going to be irrecoverable. "Cannot save record at this time" depends on the actual cause, but usually is a self-inflicted wound caused by poor programming practices. "Cannot convert open database to MDE" is a failure to understand the restrictions on the product, and again there is nothing you would have been able to do in code to fix it.

If all you wanted was to log the errors, Eberhard's link leads to a web site where you can get a short term free trial version of the product. But it will cost a couple of hundred dollars (US $) if you really want this. I do not have an opinion either way on the product and it should be noted that as a general rule we do not advertise products here. It is only because it is a specific answer to your question that we would even mention it.

The only other way to do this is to "roll your own" by getting an appropriate language compiler to write something that would intercept traps and trigger the correct environment launch.

 

[nrgins]

The purpose would be to see where the error is occurring, and, thus, be able to address it. Right now I've got a mystery "Object Required" error occasionally popping up in the middle of a process, and only on certain machines, and I don't know which line is causing it. The users are using the Runtime version, so I can't break into the code. Hence, the need to trap these system errors.

 

[isladogs]

I know you mentioned the cross-post in post #1 at both forums but it would have saved unnecessary duplication for both you & us if you had stuck to one forum. Your answer in post #6 follows on after my response at UA

 

[nrgins]

Seems I can't win! I don't mention a cross-post, I'm told to mention a cross-post. I mention a cross-post, I'm told not to cross-post. Sheesh!

I copied my reply to you to post #6 because it applied. But simply because both you and he made the same statement doesn't mean the information you provided was the same. He provided additional information that you didn't, which I wouldn't have gotten if I had only posted in UA.

Anyway, I'm sorry you feel you wasted your time. I disagree that I should have only posted in one place.

 

[The_Doc_Man]

The purpose would be to see where the error is occurring, and, thus, be able to address it. Right now I've got a mystery "Object Required" error occasionally popping up in the middle of a process, and only on certain machines, and I don't know which line is causing it. The users are using the Runtime version, so I can't break into the code. Hence, the need to trap these system errors.

OK, I'm not going to ping you on the cross-posting. However, I have to bring up a question anyway... but it isn't only for you.

@ebs17 - Eberhard, do you know if vbWatchDog would work in a run-time-only setup?

To my understanding, that run-time scenario is a restricted environment. It might not be suitable for this particular add-in. As I have stated earlier, I have not used the product so I cannot tell you anything beyond what I was able to find online. Hoping Eberhard knows more from a practical viewpoint.

 

[GPGeorge]

As I stated over at UA, VBA error handlers trap VBA errors.

The purpose would be to see where the error is occurring, and, thus, be able to address it. Right now I've got a mystery "Object Required" error occasionally popping up in the middle of a process, and only on certain machines, and I don't know which line is causing it. The users are using the Runtime version, so I can't break into the code. Hence, the need to trap these system errors.

"Object Required"? Are you sure that's a system error, not an error raised by a VBA procedure? It almost has the ring of a Reference problem, especially given that it occurs on certain machines, and not others.

 

[nrgins]

George:

Yes, it certainly sounds like a VBA error. But I added code to all the error handlers involved in the process so that they identified the procedure the error was in, as well as the section of the procedure. This error was a generic error message, which led me to believe it was a system error. (The process involves automating a Word mail merge, so I could see how an error outside of VBA code might have come into play.)

However, I just realized that there was one procedure in the set of routines and subroutines involved here that I didn't customize the error message of. So that might have been it. Thanks for the thought.

As for a broken reference, I considered that, and I added code at startup to check for broken references. There are none. Besides, this is an intermittent problem. So a broken reference would have errored out right off the bat.

 

[ebs17]

The process involves automating a Word mail merge

Another version can lead to an error, which can be prevented by using Late Binding. In extreme cases, you would have to test whether Word is installed at all.

 

[The_Doc_Man]

George:

Yes, it certainly sounds like a VBA error. But I added code to all the error handlers involved in the process so that they identified the procedure the error was in, as well as the section of the procedure. This error was a generic error message, which led me to believe it was a system error. (The process involves automating a Word mail merge, so I could see how an error outside of VBA code might have come into play.)

However, I just realized that there was one procedure in the set of routines and subroutines involved here that I didn't customize the error message of. So that might have been it. Thanks for the thought.

As for a broken reference, I considered that, and I added code at startup to check for broken references. There are none. Besides, this is an intermittent problem. So a broken reference would have errored out right off the bat.

The "Missing Object" error is all too familiar to me. Every Office component has that error. Often, error 1004.

I cannot tell you where your error is, but I have worked at different times using Word, Excel, and Outlook objects from Access. I can tell you that it is common for a multi-part qualified object reference to get caught when one of the qualifiers suddenly goes out of scope or is deresolved.

Imagine something like WordAppObj.Documents(x).Paragraphs( y ).Range ... so you create a stand-in object for the document (using a SET verb) to reduce the size of the typed referencing sequence - but then somehow one of the objects in the stand-in's definition is no longer there because you forgot to declare something Public and you just went out of scope, or because the code closed one of the qualifier components in the middle of the long qualifier sequence. For me, Excel was the worst about doing this, but Word was a close second.

 

[nrgins]

Another version can lead to an error, which can be prevented by using Late Binding. In extreme cases, you would have to test whether Word is installed at all.

Yes, I'm using late binding and Word is installed on all machines. And, as I said, it's an intermittent problem.

 

[nrgins]

The "Missing Object" error is all too familiar to me. Every Office component has that error. Often, error 1004.

I cannot tell you where your error is, but I have worked at different times using Word, Excel, and Outlook objects from Access. I can tell you that it is common for a multi-part qualified object reference to get caught when one of the qualifiers suddenly goes out of scope or is deresolved.

Imagine something like WordAppObj.Documents(x).Paragraphs.Range ... so you create a stand-in object for the document (using a SET verb) to reduce the size of the typed referencing sequence - but then somehow one of the objects in the stand-in's definition is no longer there because you forgot to declare something Public and you just went out of scope, or because the code closed one of the qualifier components in the middle of the long qualifier sequence. For me, Excel was the worst about doing this, but Word was a close second.

Thanks for the notes. I don't think that's it. But I think I figured it out, as well as to why it's only happening on certain machines and only sometimes.

The process involves opening Word, creating a doc, closing Word, then doing the same with another doc.

These were previously two standalone functions (one for each doc) which needed to be combined in a set, along with a couple of Access reports (exported to PDF).

So, when I created it, I just called each function, leaving the close Word at the end alone. Didn't think it was a big deal, even though it took an extra second or so compared to leaving Word open between docs.

What I think is happening (not yet confirmed) is that sometimes Word is still closing after the first doc when the second procedure starts. It looks for an existing instance of Word using GetObject, finds the instance that's still closing, and sets a reference to it. Then, when it goes to actually use the object variable, that instance of Word is not longer open, and, hence, the error.

The error always happens with the second doc, never with the first. And the fact that it only happens on certain machines makes sense if those machines' hard drives are slower or there's some other factor involved. And it only happened once in a while, meaning most of the time Word finished closing before the GetObject call.

Previously this was happening with about 3 or 4 machines (out of about a dozen), and so I put a bunch of DoEvent calls in there. That stopped the error on all but one machine. So some reason that machine still occasionally got the error, even with the DoEvent calls in there.

So I changed the code to leave Word open between the docs. If my theory is correct, then the error should go away completely.

The strange thing is that when it happened, it seemed to always happen after the process was run the 2nd or 3rd time; never the first time. Strange.

Anyway, that's where it stands. We'll see what happens.

The reason I thought this was a system error, as noted in a previous reply, was because I put section labels in all the relevant routines to note the section of code it was happening in, and modified the message box and logged the error. But I was getting the generic error message box.

Turns out I didn't modify THE MAIN ROUTINE for generating the Word docs!! That routine has been in place for about 10 years, and I didn't think it would be the problem. So I didn't both annotating it. Turns out, that's where it seems the problem was. D'oh!

 

[ebs17]

The process involves opening Word, creating a doc, closing Word, then doing the same with another doc.

It's also faster and more efficient to use a dedicated instance of Word that is persistent in the Access application all the time.

 

[nrgins]

It's also faster and more efficient to use a dedicated instance of Word that is persistent in the Access application all the time.

This is true. But most of the time the users generate Word docs from Access, and then review them and then save and/or print them and close Word. So that might not be feasible.

Also, wouldn't that tax system resources? These users are running with 8 GB of RAM, so not a huge amount.

 

[The_Doc_Man]

I have built situations where Access was controlling Word in an 8 GB environment. Typically, Access itself isn't that big and neither is Word. The only thing that would be ugly is if your Access back-end was approaching 1 GB and you tried to open a LOT of large Word documents simultaneously. If you are worried, here is a way to ease your mind. Open Access and Word via your app, then break away from that to the desktop and open up Windows Task Manager. On the "Processes" tab will be a column that shows the size of each process. Opening Word as an app object causes it to open in its own separate task and memory space, so Access and Word will be listed separately. I would be extremely surprised if the combination was over 1 GB for the kind of app you are describing. Now, a huge inventory app? That might get pretty big. But most of the time, I would say Word + Access at the same time would not be a problem.

Now on a 4 GB machine, because of all the OTHER crap that Windows brings with it even when it is idling, MAYBE you might have to be careful about opening and closing too many things. That Windows Task Manager "Processes" display is an eye-opener the first time you see it. But a lot of the stuff is just service apps that are incredibly tiny, fractions of 1 MB. AND a lot of them run from the SVCHOST app, which is an app to isolate a process from the environment around it. Like running a 32-bit app under a 64-bit Windows system. And SVCHOST is what is called "pure code" such that you can share one copy of it in memory with many different impure instances. Using memory management tricks, one copy of SVCHOST can service literally dozens of its client apps. So the amount of memory ACTUALLY used by SVCHOST is misleading anyway.

 

[nrgins]

Why would the size of the Access back end affect the amount of memory available for Access and Word?

 

[The_Doc_Man]

Access uses memory management to "map" the back-end into its virtual memory. Depending on issues in memory "locus of focus" you can find that it needs to bring certain more frequently referenced items into memory, even though it still might not bring in everything. Particularly if you have multiple back-ends (each one less than 2 GB but the total being larger), that is more to be mapped based on usage frequency. Word does the same thing but on a much lesser scale.