Terminating Access Applications

[Wayne Pearson]

Hi,


I have been testing different methods of terminating an Access application and
I've come across a few issues that may be of interest. These issues may be
specific to my particular machine and setup - I haven't had the opportunity to
test them on other machines. I've previously made a couple of posts pertaining
to this topic but I've finally come up with an appropriate (hopefully)
subject title - Terminating Access Applications. This will be my last post on
this topic and I make it with the hope that it may benefit others who have
experienced unpredictable results when using DoCmd.Quit to terminate
Access applications.

Using DoCmd.Quit to exit a database can give unexpected results if a user has
a form with a member variable whose type is a user defined class or with a
member variable that is a dynamic array. Problems can arise if the form with
the member variable is open at the time the user decides to exit the database.
One problem arises if the form's member variable has a type that is a user
defined class; its Terminate event will not be fired. Another problem
arises if code attempts to access the member variable in the Form_Unload event;
the user will encounter an error because member variables that are dynamic
arrays and member variables whose type is a user defined class will be
destroyed BEFORE the Form_Unload event.

These problems are NOT evident if
PostMessage Application.hWndAccessApp, WM_CLOSE, &H0&, &H0& is used to
terminate an Access application. Note that PostMessage is suggested here NOT
SendMessage; SendMessage is vulnerable to the same deficiencies as DoCmd.Quit.
One benefit of using
PostMessage Application.hWndAccessApp, WM_CLOSE, &H0&, &H0& to terminate an
Access application is that member variables that are dynamic arrays and
member variables whose type is a user defined class are not destroyed until
AFTER Form_Unload. Note also that the Terminate event WILL be fired for
member variables whose type is a user defined class. The Terminate event for
these member variables will be fired when the instance is destroyed, which
occurs AFTER the Form_Unload event.

Since using PostMessage Application.hWndAccessApp, WM_CLOSE, &H0&, &H0& to
terminate an Access application appears to result in a more orderly shutdown
I favor using it over DoCmd.Quit.

To use PostMessage Application.hWndAccessApp, WM_CLOSE, &H0&, &H0& for exiting
a database you can add the following code module to an access database project.


'------------------------------- Start of Module ------------------------------
Option Compare Database
Option Explicit


Declare Function PostMessage Lib "user32" Alias "PostMessageA" _
(ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long

Const WM_CLOSE = &H10


'If the function succeeds, the return value is nonzero
Public Function ExitDatabase() As Long
ExitDatabase = PostMessage(Application.hWndAccessApp, WM_CLOSE, &H0&, &H0&)
End Function
'-------------------------------- End of Module -------------------------------


Now it is possible to exit the database by typing

status = ExitDatabase()


Note that this works well for me; your mileage may vary.

Any comments or insights into these issues are welcomed.

Comments and full source code for a simple access project that demonstrates
these findings follow.


Simple Demonstration Application


I created a simple application to demonstrate the above behavior. The
application has two forms frmPrimary and frmSecondary, a class module Test,
and a module APIDefs.

frmPrimary has two buttons, a Display button and an Exit button. Clicking
the Display button invokes cmdDisplay_Click, which then displays frmSecondary.
Clicking the Exit button invokes cmdExit_Click, which then calls DoCmd.Quit.
Code is also present to call
PostMessage Application.hWndAccessApp, WM_CLOSE, &H0&, &H0& (ExitDatabase) but
it has been commented.

frmSecondary has a class member variable m_Test, which is a class variable of
type Test

When frmSecondary is displayed, its Form_Load event procedure creates a new
instance of the member variable m_Test and populates it. When frmSecondary is
closed its Form_Unload event procedure prints m_Test's Value.

Test defines a class named Test which has one public property named Value.

APIDefs provides definitions for PostMessage, WM_CLOSE, and ExitDatabase. Note
that only ExitDatabase is defined as public.

If I open frmPrimary, press its Display button to open frmSecondary, and,
finally, press frmPrimary's Exit button to end the application then I get a
Run-time error '91': Object variable or With Block variable not set in
frmSecondary's Form_Unload event procedure. m_Test is destroyed before
Form_Unload is called. Note that m_Test's Terminate event procedure did
NOT fire.

However replacing DoCmd.Quit in frmPrimary's cmdExit_Click with
PostMessage Application.hWndAccessApp, WM_CLOSE, &H0&, &H0& (ExitDatabase)
appears to solve the problem. frmSecondary's m_Test member variable is NOT
destroyed prior to calling Form_Unload and things appear to work as expected.
Note that m_Test's Terminate event procudure is called AFTER the
Form_Unload event procedure.

Note that PostMessage is used here and NOT SendMessage. Using SendMessage gives
a Run-time error '91': Object variable or With Block variable not set in
frmSecondary's Form_Unload event routine.


I would be interested in any comments regarding these issues -
the application code is given in its entirety below.


'------------------ Start of Demonstration Application Code ------------------


'-------------------------- Start of frmPrimary Code -------------------------
Option Compare Database
Option Explicit

Private Sub cmdDisplay_Click()
DoCmd.OpenForm "frmSecondary"
End Sub

Private Sub cmdExit_Click()
Dim status As Long

' Using DoCmd.Quit results in a
' Run-time error '91': Object variable or With Block variable not set
' in frmSecondary's Form_Unload event routine
' If DoCmd.Quit is used to terminate an application then
' Form class member variables are destroyed prior to calling
' Form_Unload - Why?
'
' Note also that the Terminate event procedure for class member variables
' is NOT called when DoCmd.Quit is used to end an application - Why?
'
'
' ---------------- Terminate Application Using DoCmd.Quit ----------------
DoCmd.Quit
'
'
' Using PostMessage Application.hWndAccessApp, WM_CLOSE, &H0&, &H0&, however,
' allows the application to exit gracefully
'
' Note that the Terminate event procedure for class variables IS called when
' PostMessage Application.hWndAccessApp, WM_CLOSE, &H0&, &H0& is used to
' end an application. The Class Terminate event procudure is called AFTER
' the Form_Unload event procedure - Good!!!
'
' Based on these findings I favor terminating Access applications with
' PostMessage Application.hWndAccessApp, WM_CLOSE, &H0&, &H0& rather
' than DoCmd.Quit.
'
' Are there any known problems with using this method of terminating
' an application?
'
'
' ---------------- Terminate Application Using PostMessage ----------------
' status = ExitDatabase()
End Sub
'--------------------------- End of frmPrimary Code --------------------------


'------------------------- Start of frmSecondary Code ------------------------
Option Compare Database
Option Explicit

'Note that Test is defined in the Class Module Test
Dim m_Test As Test

Private Sub Form_Load()
' Set frmSecondary's m_test member variable to a new instance of Test and
' populate it

Set m_Test = New Test

m_Test.Value = 1
End Sub

Private Sub Form_Unload(Cancel As Integer)
' The next line will fail with
' Run-time error '91': Object variable or With Block variable not set - Why?
' When DoCmd.Quit is used to end the application the m_Test instance gets
' destroyed BEFORE Form_Unload is called - Why?
'
' Note also that the Terminate event procedure for class member variables is
' NOT called when DoCmd.Quit is used to end an application - Why?
'
' Note that if the application is closed with
' PostMessage Application.hWndAccessApp, WM_CLOSE, &H0&, &H0&
' then the m_Test instance does not get destroyed BEFORE Form_Unload is
' called and the next line executes without error - Good!!!
'
' Note that the Terminate event procedure for class variables IS called when
' PostMessage Application.hWndAccessApp, WM_CLOSE, &H0&, &H0& is used to
' end an application. The Class Terminate event procudure is called AFTER
' the Form_Unload event procedure - Good!!!
'
' Based on these findings I favor terminating Access applications with
' PostMessage Application.hWndAccessApp, WM_CLOSE, &H0&, &H0& rather
' than DoCmd.Quit.
'
' Are there any known problems with using this method of terminating
' an application?

Debug.Print m_Test.Value

Set m_Test = Nothing
End Sub
'-------------------------- End of frmSecondary Code -------------------------


'---------------------- Start of Class Module Test Code ----------------------
Option Compare Database
Option Explicit

Dim m_value As Long

Public Property Let Value(ByVal pValue As Long)
m_value = pValue
End Property

Public Property Get Value() As Long
Value = m_value
End Property

Private Sub Class_Initialize()
Debug.Print "Class Initialized"
End Sub

Private Sub Class_Terminate()
Debug.Print "Class Terminated"
End Sub
'----------------------- End of Class Module Test Code -----------------------


'------------------------ Start of Module APIDefs Code -----------------------
Option Compare Database
Option Explicit


Declare Function PostMessage Lib "user32" Alias "PostMessageA" _
(ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long

Const WM_CLOSE = &H10


'If the function succeeds, the return value is nonzero
Public Function ExitDatabase() As Long
ExitDatabase = PostMessage(Application.hWndAccessApp, WM_CLOSE, &H0&, &H0&)
End Function
'------------------------- End of Module APIDefs Code ------------------------


'------------------- End of Demonstration Application Code -------------------


Thanks

Wayne Pearson

 

[Albert D. Kallal]

I going to skip reading most of this long post. It seems you got WAY TOO
much time on your hands.

First...why not use the forms close event...and why use the un-load event?
The un-load event is NOT for clean up, but only to cancel the un-load...

2nd, where, and why are you executing the application docmd.quit?

3rd, a forms level array declared is STILL available in both the close event
code, and the un-load event code. I don't know why you state this. Even more
telling is that the class objects terminate event DOES get executed.
However, you do need to explicitly clear out your class object code. You
should always go:

set clsMyClassObject = nothing

The above code (if it runs!) will ensure the terminate event gets run.

If you are not explicit clearing up your class objects, then that is a bit
sloppy.

Do NOT use the fact of vars going out of scope to clean up objects.

For sure, if you are coding a docmd.quit, I would first of course at give
ms-access some curtsies...and fire the close event of the form first!.

All we really are saying here is that docmd.quit is a bail out, and it means
to bail out...and yes, the event code does then loose the variable scope.

So, why not just clean up things before you close?

So, in the case of a form...
Just go:

DoCmd.Close
DoCmd.Quit

The above will then fire the close event, and the un-load event (which fires
before the close event). And, all of the vars, and class objects are
availing in this case.

So, even if you do a "quit" command, the above setting of the object to
nothing will cause the terminate event of the object to fire also.

And, of course you do the same for any other reocrdset code etc. that you
have. Any object you create should be destroyed by you. For general vars
like strings etc, the system will do a fine job.

I would also consider coding my own custom application Quit routine that
correctly shuts down all open forms. It is a simple matter to iterate the
forms collection, and THEN execute

application.Quit

So, if you are stating that application.Quit (or docmd.Quit) is a bit
nasty...sure. But, if you are actually using the command in your code...then
as mentioned just execute the forms close event first.

If you are looking for a controlled shut down..then I would code your own
shutdown routine.

I have a simple application with 160 forms, and 27,000 lines of code. (I
consider this application simple...since as mde..it still fits on a floppy
disk). In that code I also have a form timer event that shuts down the
application after two hours of inactivity. I never had a problem, but in my
case about 90% of my forms are model, and when I launch from one for to the
next, I always done a disk write, and as a result..never a problem. I always
considered it risking to launch to another form without the current form
having written its data.

And yes, in my case the docmd.Quit will cause forms to un-load, and class
objects to go out scope...but since I ALWAYS do a disk write before opening
the next from in the sequence...never a problem...

 

[Wayne Pearson]

Hi Albert,


First I would like to thank for your comments. Your proposed solution worked
well and your comments enabled me to improve my test application. You also
correctly identified the theme of my post, which is you must be cautious when
using DoCmd.Quit to terminate an Access application. Incidentally, this
cautionary advice is also true for Application.Quit and CloseCurrentDatabase.


Next I'll state my conclusions and general comments


Note that the errors I encountered may be specific to my particular setup and
machine; I didn't have the opportunity to test my application on other
machines. I'm using Access 2000, Office 2000, and Windows 2000; each of which
has the latest service packs applied.


If one of your application's forms (lets call it frmTest) has a member variable
that is a dynamic array, ie an array dimensioned in the style
Dim m_Numbers() As Long, or a member variable whose type is a
user defined class then you must exercise caution when exiting your application
using any of DoCmd.Quit, Application.Quit, or CloseCuurentDatabase. If
frmTest is OPEN and you exit the application by calling any of these three exit
methods (possibly by pressing the exit button on a switchboard form) then
frmTest's member variable, as described above, will be destroyed before
BEFORE Form_Unload is called (and therefore BEFORE Form_Close is called).
Additionally, if the member variable has a type of user defined class then
its Terminate event will NOT fire. These problems can be avoided if a routine
that closes all open forms is executed prior to calling any of these three
exit methods. Thanks to Albert D. Kallal for proposing this solution.

Executing a routine that closes all open forms prior to calling any of the
three exit methods listed above WILL avoid the problems I experienced. However,
I strongly feel that the de facto method for exiting an Access application
should ITSELF take responsibility for closing all open forms if, in fact, this
is required to ensure a graceful exit of the application . the developer
should NOT have to write a special routine to close all forms prior to
calling the application exit method. I would assume that the user, upon
pressing the exit button, would prefer that the application exit gracefully
rather than bail out. Typically, rudely exiting an application entails
bringing up the task manager, selecting the target application, and pressing
the End Task button. This action is frequently accompanied by the muttering
of colorful expletives.

I initially tested three options for exiting the application
1) selecting file . exit, 2) pressing the x in the upper right corner of
Access, and 3) pressing the Exit button which generated a call to DoCmd.Quit.
Option 3 (DoCmd.Quit) is the ONLY exit method of the initial three that
caused me trouble. Later, I discovered that Application.Quit and
CloseCuurentDatabase were also problematic.

Note that posting a WM_CLOSE message to the application's main window
(using PostMessage NOT SendMessage) enables the application to exit gracefully
without requiring the use of a special procedure to close all open forms.
I'm not sure if there are any caveats to using this technique but, so far,
it works for me. If I exit the application in this manner then all open forms
are closed, all Form member variables are still available in both Form_Unload
and Form_Close, and the Terminate event for Form class member variables fires
AFTER the Form_Close event.


I have finished stating my conclusions and general comments at this point and
will now endeavor to respond to your specific comments below.

First...why not use the forms close event...and why use the un-load event?
The un-load event is NOT for clean up, but only to cancel the un-load...

I like your suggestion to place the code for Form member variable cleanup in
Form_Close. I will most likely adopt this technique in future projects. I've
actually recoded my test project to use Form_Open and Form_Close instead of
Form_Load and Form_Unload. I also added error handling to catch an issue
concerning execution of my explicit cleanup code for frmSecondary's m_Test
member variable. However, in this instance, moving frmSecondary's code from
Form_Load and Form_Unload to Form_Open and Form_Close, respectively, AND
adding the error handling, did NOT change the results (with the exception
that the errors occurred in Form_Close rather than Form_Unload).

2nd, where, and why are you executing the application docmd.quit?

First the where

In my case I have an application that has two forms; frmPrimary and
frmSecondary. frmPrimary serves essentially the same purpose as a
switchboard form and it has two buttons, a Display button and an Exit button.
Clicking frmPrimary's Display button invokes cmdDisplay_Click, which then
displays frmSecondary. Clicking the frmPrimary's Exit button invokes
cmdExit_Click, which then calls DoCmd.Quit. frmSecondary has a member variable,
m_Test, that is a user defined class variable of type Test. frmSecondary's
Form_Open event instantiates and populates this variable. frmSecondary's
Form_Close event procedure attempts to print m_Test's Value. It later attempts
to explicitly cleanup m_Test by executing the statement Set m_Test = Nothing.

I first bring up frmPrimary, press its Display button to show frmSecondary and
then press frmPrimary's exit button while frmSecondary is still open.

Next the why

Well, if one uses the toolbox to add a quit button to a form and selects
Application . Quit Application then a quit button is added to the form. This
button's OnClick event procedure contains a call to DoCmd.Quit. If the toolbox
supplies a button with this code then it MUST be Ok, right? Note that there is
NO code generated that iterates through all open forms and closes them
prior to calling DoCmd.Quit. I also checked out the wizard generated
Asset Tracking1 template database, and discovered that CloseCuurentDatabase is
the code generated to respond to the Exit button. Again, note that there is
NO code generated that iterates through all open forms and closes them
prior to calling CloseCuurentDatabase.

3rd, a forms level array declared is STILL available in both the close event
code, and the un-load event code. I don't know why you state this. Even more
telling is that the class objects terminate event DOES get executed.
However, you do need to explicitly clear out your class object code.

If you check my original post you will see that I did put Set m_Test = Nothing
in the Form_Unload event procedure and, in fact, did not depend on the variable
going out of scope to clean it up. I do admit, however, that I screwed up and
didn't put error handling in my routine . even test code deserves
proper error handling. When the error was encountered on the line
Debug.Print m_Test.Value, code execution ceased and
Set m_Test = Nothing wasn't reached. I've since recoded my test routine,
adding error handling to ensure that Set m_Test = Nothing does, in fact,
get executed. However, in my case the m_Test variable is destroyed BEFORE
reaching the statement to explicitly clean it up and its Terminate event does
NOT fire. I use the term dynamic array to refer to an array dimensioned
in the style Dim m_Number() As Long. If a user has such a dynamic array or a
class member variable in his Form AND he exits his application following the
scenario that I indicated, EVEN with the added error handling to ensure that
Set m_Test = Nothing DOES get executed, then the member variable WILL be
destroyed BEFORE Form_Unload is called (and therefore BEFORE Form_Close
is called). Additionally, the class member variable's Terminate event will
NOT fire.


If you follow the scenario that I indicated then I'm unsure of why you're don't
encounter the errors I described. Possibly the errors are specific to my
machine and setup. I'm using Access 2000, Office 2000, and Windows 2000; each
of which has the latest service packs applied.


Thanks,

Wayne Pearson

 

[Albert D. Kallal]

Actually, you do have a very good point. One that well should perhaps be
written up, and documented on the access web site.

Likely, the behaviour you experience does occur for all versions of
ms-access.

I will well say that your comments and notes on this issue is/was most
appreciated.

While I can and did suggest some workarounds.....your comments and work on
this is something that any serious developer should know.

I will certainly add your comments and notes to my "help" library.

Further, as mentioned, I virtually always do a form write to disk...but a
LOT of developers do NOT, and you right point out that the behaviour is
different.

Those vars going out of scope is a surprise to me. I was NOT aware of this
issue. I never got bitten by the bug, but in fact I will take a look at a
few of my forms to make sure I don't get bit!

Once again Wayne...thanks for posting this stuff....we all benefit by people
like you who share this stuff with us!

In fact, I even think that this beavhiour should be documented in the help
file (under the docmd.Quit).