Link to home
Start Free TrialLog in
Avatar of shambalad
shambaladFlag for United States of America

asked on

How to preserve class objects after an error is handled

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.
SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
TempVars also work in 2007.
<<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.
"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
Avatar of shambalad

ASKER

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
"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
<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.
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?
"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
OK. I'm going to put together a small db to illustrate what I'm getting at. This shouldn't take long.
ok.  Be sure to include exact instructions.

mx
No problem. I appreciate your taking the time to work with me on this.
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
Going to load the Error trapping options to a table to test all three options.
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.
I'll try to limit my scope creep to that...
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
Correct. That is what I'm focusing on in this iteration. I'm working on it. Sorry it's taking so long.
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
ok. what should I be doing with this demo ?
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.
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.
User generated imageThis 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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
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
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
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).
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.
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.
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 (https://www.experts-exchange.com/questions/21184044/Class-Terminate-not-firing.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
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....
<<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.
< 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.
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?
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
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)
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
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.
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
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.
Are you using Tempvars for your "global" variables?
Using Office 2003, so they're not available.
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.
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
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.