How to preserve class objects after an error is handled

shambalad
shambalad used Ask the Experts™
on
The base problem in a nutshell:
When Access encounters an error, it hammers almost all of its pointers and references. Notice I say ‘almost’. I do know if I have a form with an associated module, and I have a variable scoped at the module level in that module, it will get hammered after an error is handled anywhere else within that project. If, however, I store the value I am trying to preserve in a textbox, I can still retrieve the textbox value even after handling an error. Based on this knowledge, I have taken to loading any values I wish to preserve into controls in a hidden form I keep active while the application is running.
Unfortunately it doesn’t appear that I can do this for any custom classes I create (unless I can find some kind of control that will hold a class). This is something of a show-stopper for me when considering the use of custom collection classes. This is a shame because there is so much that I can and could do with these classes.
This is also an issue for a current project I am working on which is using Oracle for the back-end. I have my connection and database objects scoped globally, and if/when I get an error, I have to re-create these objects, which is something I would very much like to avoid. I did try scoping the Oracle objects privately within the hidden form. But they still get hammered if an Access error is handled.
I need to find a solution to this problem.
It has been my observation that if I don’t evoke the error handler, this memory clearing process doesn’t occur. That is to say, if I have a function wherein I code in a “On Error Resume Next” at the top, I can generate an error and I don’t lose all of my objects. So I guess if I wanted to go to an extreme, I could just use “On Error Resume Next” for all of my procedures, and then after each line of actual code, I could insert the line “If Err <> 0 Then GoTo ErrorHandler”. Maybe that is the solution, but I’d rather not go that route if I don’t have to.
Has anyone else dealt with this and found a workable solution? If you could point me towards a reference that definitively lays out what Access does at the metal level when it is handling an error that would be most appreciated too.
For what it’s worth, I am currently working on an Access 2003 database in an XP environment, but I’ll welcome input for any version up to and including Access 2010. If 2010 can handle this in a way that 2003 can’t, I would consider that a compelling argument to install a run-time solution.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007
Commented:
"If 2010 can handle this in a way that 2003 can’t, I would consider that a compelling argument to install a run-time solution. "

A2010 has the TempVars collection ... kind of a 'permanent' global variable - ie, does not get hosed when errors or a reset occur.  And they can be called directly from  queries also  as a bonus.  And they are simple to use.  

I suggest you do not put On Error Resume Next everywhere as a work around.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
TempVars also work in 2007.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<When Access encounters an error, it hammers almost all of its pointers and references. Notice I say ‘almost’. I do know if I have a form with an associated module, and I have a variable scoped at the module level in that module, it will get hammered after an error is handled anywhere else within that project. >>

  That's not quite correct.  Just to be clear, everything gets cleared when execution in VBA is stopped.

  Handling an error in of iteself whether you do it in-line or with a seperate error handler in the procedure will not affect any variables or objects and there is no difference as well between the full version and the runtime (which is actually the same).

Jim.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"Just to be clear, everything gets cleared when execution in VBA is stopped."
Stopped as in ?  IE, what do you mean by 'stopped' exactly ?

mx

Author

Commented:
Sorry for the delay in getting back to this. I left the client site shortly after I posted the original question, and am just getting back to my home office.
It's been my experience that if my application encounters an error that causes it to 'goto' the ErrorHandler, all variables scoped "public" revert to their uninitialized state. Thus a variable defined as string will have a value of "", a long becomes 0, a boolean becomes False, and objects become 'nothing'. I have found that this happens for variables scoped "Private" at the module level too. But if I have stored the value in a textbox in a form, I can still retrieve the value. So I use textboxes for any string or long variables, and checkboxes for booleans. This behavior is consistent for 2003, 2007 and 2010. It may not be documented that way, but that's the way it works. I've been using text and check boxes in a hidden form to hold my critical global variable values for some time now without any problem.
My issue is with objects which I can't load to form controls in the same manner.
I will look at the TempVars collection. That sounds promising. I will look at that this evening once I've had supper.
Thanks,
Todd
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"It's been my experience that if my application encounters an error that causes it to 'goto' the ErrorHandler, all variables scoped "public" revert to their uninitialized state"

That should not be the case, and that's not the way it works.  That's part of the whole point of error handlers - to prevent this ... so, that errors don't crash the system.

Something else is going on ...

mx

Author

Commented:
<there is no difference as well between the full version and the runtime (which is actually the same).>
Yes, Jim, I know this. I have implemented run-time projects. The reason I mentioned that is because I am working with an enormous financial services corporation that is currently using Office 2003 on an XP platform. They're going to upgrade sometime in the next couple of years, but that is not cogent to the task at hand.
If I were to opt for a 2007, or 2010 solution, it would have to be run-time solution. As it is, I don't think they'd go for it anyway. But I'm going to check out these TempVars. Maybe I can make a case.

And I really have to differ with you on this statement:
< That's not quite correct.  Just to be clear, everything gets cleared when execution in VBA is stopped.>
I refer you to my previous post. Try it yourself. Values loaded to form controls do not get cleared.

Author

Commented:
mx -
You're giving me glimmers of hope here. So you're saying that Public variables should not get cleared? I guess I need to run some more tests, because that's what is happening to me. Or at least it seems to me that's what is happening.
This first became a real issue for me last year when I created an application in 2007 that used multiple sets of custom collection classes with parent-child relationships. It was an elegant construct, but it disintegrated if an error was handled.
Now I'm working on a project in 2003 and it's kicking me in the butt again.
But you say it shouldn't happen this way..
Which brings me back to this: Can anyone provide the documentation that definitively, unequivocally states exactly what is happening under the covers in Access when an error is being handled?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"Values loaded to form controls do not get cleared. "
Of course, not. But neither do variables when an error occurs, and you have error handling.  Or, I'm not following a specific case you are referring to ...?

mx

Author

Commented:
OK. I'm going to put together a small db to illustrate what I'm getting at. This shouldn't take long.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
ok.  Be sure to include exact instructions.

mx

Author

Commented:
No problem. I appreciate your taking the time to work with me on this.

Author

Commented:
As I'm putting this together, noticing that I've been setting my error trapping option to break in class modules. Wonder what kind of impact that is having:


   Public Enum enmErrorTrappingOption
      enmBreakOnAllErrors = 0
      enmBreakInClassModules = 1
      enmBreakOnUnhandledErrors = 2
   End Enum

Application.SetOption "Error Trapping", enmBreakInClassModules

Author

Commented:
Going to load the Error trapping options to a table to test all three options.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Break on unhandled errors.

mx

Author

Commented:
I'll start with that. But the purpose of this small app is to explore Error handling, so I'm including a way to explore how this trapping option might impact the process.

Author

Commented:
I'll try to limit my scope creep to that...
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Well ... 'explore error handling'.  Ok, but that is an entire subject by itself.  As I understand it, you are saying that ... when an error occurs,  variables are reset.  So, that's what we should be exploring ... right ?
mx

Author

Commented:
Correct. That is what I'm focusing on in this iteration. I'm working on it. Sorry it's taking so long.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
no problem

Author

Commented:
Wouldn'cha know it. Built a little app to prove my point and it proves the opposite; or so it seems. The attached is an mdb (2003 format) created in Access 2010 (32 bit) on a windows 7 laptop. Nothing gets hammered (except me, I guess). It's way past late. I need to play with this some more. But at least now I have the rudiments of a test bed to begin trying to figure out what it is I think I'm seeing.
I need sleep....
Error-Handling-Demo.zip
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
ok. what should I be doing with this demo ?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Results ....


1

Author

Commented:
That's right: No error. Everything's working fine.
What I do know is that something, somehow is hammering global variables in my apps. At this point, I have to toss all asumptions out the window. I don't know what the mechanism is that's getting me there. But the problem is real, so now I have to start figuring out what it is I'm doing to create the problems I'm encountering. There's a great many more moving parts in my standard apps. I really stripped things down for this. Now I guess I need to start adding more layers until something breaks.
Just what I don't have the time for....
Oh well. I do have to fix this and I can't put it off.

Author

Commented:
I added a checkbox that toggles a variable to execute a 'Stop' command in the Error Handler procedure. Either way, that is, stopping the VBA code or not stopping the code, all values are being preserved.
Added checkbox to stop codeThis raises a question:
Jim - you stated < everything gets cleared when execution in VBA is stopped.>
That's not happening here. What is different in this demo?
Oh, and Jim, I apologize for my tone in my earlier post. I just need to get reminded every now and then how little I really do know about Access.
BTW: I moved this mdb from the laptop to the PC running 2003 in XP. I had to update the references accordingly.

I'm going to continue to shake this thing until I can make sense of it all.
ErrorHandling.zip
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<I added a checkbox that toggles a variable to execute a 'Stop' command in the Error Handler procedure. Either way, that is, stopping the VBA code or not stopping the code, all values are being preserved. >>

Let's clear up what I mean by a stop.   A STOP statement in a procedure is not stopping VBA execution, it pasues VBA execution.

When you hit a stop statement, VBA execution is paused and you can resume from that point (i.e. F8 or F5).  All variables, objects, etc retain their values.  VBA is still executing and in a running state.

However if I stop the VBA execution, either with the stop execution button in the editor or an END statement in a procedure, then VBA execution stops and all variables and objects are cleared.

 Form controls do not loose their values because they are not VBA based, but Access objects.

From the online help:

Stop

Remarks

You can place Stop statements anywhere in procedures to suspend execution. Using the Stop statement is similar to setting a breakpoint in the code.

The Stop statement suspends execution, but unlike End, it doesn't close any files or clear variables, unless it is in a compiled executable (.exe) file.

End

Terminates execution immediately. Never required by itself but may be placed anywhere in a procedure to end code execution, close files opened with the Open statement and to clear variables.


Remarks

When executed, the End statement resets all module-level variables and all static local variables in all modules. To preserve the value of these variables, use the Stop statement instead. You can then resume execution while preserving the value of those variables.

Note   The End statement stops code execution abruptly, without invoking the Unload, QueryUnload, or Terminate event, or any other Visual Basic code. Code you have placed in the Unload, QueryUnload, and Terminate events of forms and class modules is not executed. Objects created from class modules are destroyed, files opened using the Open statement are closed, and memory used by your program is freed. Object references held by other programs are invalidated.

The End statement provides a way to force your program to halt. For normal termination of a Visual Basic program, you should unload all forms. Your program closes as soon as there are no other programs holding references to objects created from your public class modules and no code executing.

Hope that helps clear up the confusion.

Jim.

Author

Commented:
Of course, if I click the Reset button while the code is stopped, that'll clear everything. but I don't think that's what I've been doing.
I've incorporated this form into the project I'm working on. I need to get some development work done there this morning. Unless I've stepped from one universe into another in the last day, this whole problem is going to rear its head somewhere along the line today. When that happens, I'm going to use this tool to extract some more clues. I'll update this as I go along.
In the meantime, I really would like to review some definitive documentation on what happens when Error Handling is evoked, if there's any to be found.
Thanks,
Todd

Author

Commented:
I didn't see your post, Jim, when I posted my last one.
I'm still in something of a quandary about what has been happening to me recently. I've never had occasion to use the END statement; and I usually don't hit the Reset button either (sometimes, but not usually).
I can't seem to replicate the errors I've been experiencing with this demo app I created. I've tweaked it a little more, and I've incorporated into my current work. I'm just going to have to leave it there in waiting until I get hit in the face with the problem again. I don't think it's going to take long. I'll update here as I figure out more on what it is I must be doing wrong.
ErrorHandling.zip
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Todd ... here is one of the better error handling write ups - by Luke Chung:

http://www.fmsinc.com/tpapers/vbacode/Debug.asp
I tend to review this myself periodically.

And this error number db is pretty handy also - I have it on my desktop :-)

http://www.fmsinc.com/MicrosoftAccess/Errors/ErrorNumber_Description.html
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Oh, one more thing ... this:

http://www.everythingaccess.com/vbwatchdog.htm

Check out my review.  This app is VERY cool.  I'm slowly incorporating it into my apps. This is the serious way to deal with error handling in a consistent manner.  And NO external DLL or OCX is required !!

mx

Author

Commented:
Thanks for the additional information. The global error handler I implement in my apps does incorporate a call stack procedure. I use MZ-Tools to add the push and pop calls for all of my procedures. To be honest, I am doing all the things suggested here. I will have to check out vbWatchdog to see if there's something there I've overlooked. It's pretty clear to me at this point I must be doing something wrong, and I will figure it out. If nothing else, I can be doggedly persistent when I'm on the trail of a bug (but then I guess you have to be if you chose programming for a career).
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Where and how are you storing the custom classes now? Are you building a global variable to hold the collections?

So the process is that you (a) build the collections (arrays, perhaps?) and then (b) build the items which go into that collection and then (c) add those items into the collection?

What are you using for the collection classes? I generally use a simple array of that object type, and I can't recall a runtime error resetting those arrays (but I could be wrong).

I do agree that in many cases this is caused by a fault in the error handling, but it seems that you have that well in control.

Author

Commented:
I'm going to add some classes to the sample db I've been updating for this question. I'll try to get to that later today. Flat out this morning.

Author

Commented:
One of the things I use custom collection classes for is the management of databases and recordsets, especially if I’m pulling data from more than one data source.
For now, I’ve added set of classes to the db that I put together when I was first figuring out how to create and use them. These particular classes have a small form associated with them which makes it very easy to visualize what is going on beneath the covers. I borrowed that technique from a post on EE by Harfang (http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21184044.html).
I also implemented the procedure stack. It looks better on a form than the standard message box, but my implementation of the form doesn’t work in 2003, and it’s not worth the effort (to me, at least) to convert the form.
For what it’s worth, I’ve also attached the original class demo mdb I created.
Of course, still nothing’s getting hammered; but I will find this bug. It has fully aggravated me and I will not rest until I’ve annihilated it.
ErrorHandling.zip
Collection-classes-demo.zip

Author

Commented:
I have encountered an event that cleared the objects. This was within a procedure that had an error handler. I am working on an app that uses the "Oracle OLE objects" API. I had an error referencing an object created by this API. That hammered the objects (and all of my globally defined variables, too)
I know I was getting this type of an error last year, though, when I was not using this API. The investigation continues....
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<I had an error referencing an object created by this API.>>

 What kind of error?  Did the error handler trap the error?  Did program execution continue after that?

 I really don't think your running into anything other then VBA code execution is being stopped.

 If VBA was that fundamentally broken in terms of handling errors, this would have come up a long time ago.

Jim.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
agree.

Author

Commented:
< If VBA was that fundamentally broken in terms of handling errors, this would have come up a long time ago.>
I certainly agree with you on that.
Yes, an exception was thrown and the handler caught it.
This event has given me some ideas on how to proceed. I'm going to come into the office Monday and do some work on this. I'll post my findings.
Have a good weekend.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Indy 500 on Sunday  .. !!!!!!!!!!
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
One of the things I use custom collection classes for is the management of databases and recordsets, especially if I’m pulling data from more than one data source.
Do you mean you store recordsets and/or connections in a class? If so, how do you do this? I didn't see an example of that in the ones you uploaded, but I may have missed it.

My concern would be if you're doing something like this in the General Declarations section of your class ...

Dim rst As DAO.Recordset

...  and then filling that recordset and simply using your class as a repository of the recordset (or to parcel out that recordset as needed) you could certainly run into troubles if that connection is broken for some reason. It would seem reasonable that Access would detect that error and clear out the connections/recordsets, and perhaps even the hosts for those connections/recordsets (i.e. your classes).

Are you storing the returned data from those recordsets locally - not in recordsets, but in UDTs, variables, arrays, etc?

Author

Commented:
Briefly, they were all public classes. In the project I developed and used them for last year, there was never a lost connection, nor, as far as I know, an error generated within the classes themselves. The errors were external to the classes.
There were no arrays involved. The returned objects were DAO databases and recordsets. At first glance, it would appear that creating collection classes of DAO databases and recordsets is a little redundant (since that is what they basically are anyway). But they did serve a purpose, and coding with them was a snap once they were set up.  I’ll lay out some of the rationale later.
I will add the dataset classes to the demo, but I first need to make them a little more generic (I did some customization for the project I first developed them for). These classes are a long term project for me.  I am no way near done with them, but I’ll show you what I have.
I'm under a certain amount of pressure with my current client. I am a little behind on the timeline and need to focus on producing deliverables. I will try to squeeze some time in later today to post more here.
Thank you for taking the time to work with me on this question.
Todd
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
I understand the pressure, and certainly understand the need to service the client.

I await with curiosity the reasoning for returning DAO objects from a class. One of the main purposes of a data-centric class would be to somewhat "disconnect" the client from the datastore and allow you to work with the data without a connection (i.e. caching the data locally). Maintaining a connection by returining a DAO recordset, for example, would certainly cause that class to go awry if the connection were lost (i.e. you reference to the Oracle API above)

Author

Commented:
I have not been able to attend to this question for some time, and I regret that, but I have been up to my eyeballs in work. While hewing to an aggressive timeline set by my current client, I've also been renovating two rental units. Wife and kids need their time too...
There will come a time when I will return to this question. I pulled the features that were giving me the grief from my latest project, but the underlying object model alluded to in this question is a long-term project in and of itself that is near and dear to me and I am most certainly not going to abandon it.
In the meantime, the status of this question must be updated. If, for no other reason than I cannot ask any new questions while this one remains apparently abandoned (and I happen to have a question I need a quick answer for right now).
Anyway, in the interest of maintaining order on the forum, I guess it would probably be best for me to request the question be closed. Then when I am able to focus on this again, I would like to submit a request to re-open the question.
I know the three of you spent a fair amount of time working with me on this, and I definitely do not want to have any of you being annoyed with me (I need you guys!). If you think there is a better way for me to proceed, please let me know.
Your humble apprentice,
Todd
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I would advise against deleting as it has been an interesting read, and would be good to have this thread to reference in a future post.

Instead, I would recommend accepting one of your responses.  That will keep it in the database and available for you to reference and for others to search.

Author

Commented:
Well, that answered that question; i.e.
<< If you think there is a better way for me to proceed, please let me know. >>
I will keep this open. I'll put a little time in on this later today, and move it forward.
Thanks again for all your help, time and patience.
Todd

Author

Commented:
A colleague working in the database I am currently working had an error after which the globally variables reset to default. Trying to find out the sequence of events that led to this state.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Are you using Tempvars for your "global" variables?

Author

Commented:
Using Office 2003, so they're not available.

Author

Commented:
I've been logging consecutive 10 hour days since July 4th on my client's project (except for last Saturday and Sunday when it was take a break or get a divorce).  At least I get paid by the hour. It looks like I'm finally going to get back on the timeline by the end of this week. Putting in some time on this question is near the top of my list as soon I hit that point.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
'Take the break' :-)

mx

Author

Commented:
I've got to move beyond this question.
Jim - I have an inkling what you describe in stopping the code may be what is happening here. Just haven't been able to really prove in a 'live' instance.
MX - Tempvars is a very good suggestion for my future consideration.
Should that epiphany come where I nail this ghost in the machine down, I'll return to this spot.
My sincerest gratitude to those of you who helped me on this question. I did learn some things here.
Todd

Author

Commented:
Just had one of those Eureka! moments this evening.
I’ve been doing a lot of work in Excel recently and was poking around the web when I came across this thread in the PCReview forum. It appears that my problems may have had to do with some Excel behavior I was not familiar with ( kb231089, see also q_23574623). Basically, if you open the VBA IDE in Excel, the global variables get reset. In my case this makes sense, since I’ve only encountered this problem when developing Access 2003 apps working with Excel objects.
This would also explain why the Stop command appeared to be triggering this problem, since the VBA IDE opens when Stop executes.
As an aside: One of the common themes to the posts cited above was adding controls to worksheets at runtime. Here is an interesting workaround to this problem using VBS to create the controls.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial