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.