Shut it Down!
Closing remote instances of Microsoft Access
Introduction
One of the problems involved in managing a Microsoft Access application distributed
over a network, especially one that’s still in various stages of development, is
the inability to make changes to a table structure or perform other maintenance
while someone else has it open. What makes it even worse is the users who
leave the application open continually no matter how often you tell them not to
or tell you they don’t have it open when it’s actually minimized on their computer
screen. Sometimes just explaining the concept of a file being open is challenging
and the blank stare you hear coming over the phone line is not encouraging.
It can be tempting to not allow the users access at all but that’s generally not
an option.
After running into this problem once again just recently, I started thinking about
what type of feature I could design to close down remote copies of the database
when necessary. All of the applications I’ve designed for distribution are
databases that have been split into front and back ends. The back-end contains
only the data tables and is located in a directory on the network (preferably a
hidden one to avoid the clutches of the occasional power user / control freak).
The front-end with all the code, forms and reports is distributed to the users to
run from their local machines. Access does not, at this point, provide a method
for administrators to shut remote users down and in some cases, although certainly
not in mine, this may be for the best.
So, directly controlling other users’ interfaces was out of the question.
I couldn’t think of any way that putting any type of programming in the back-end
would help as the only connection between the back-end and the users is the collection
of table links. Then I thought about a combination of the two.
What I came up with is a somewhat elegant, if roundabout and less than instant,
method of causing all remote front-ends to close on command. The solution
involves an options table which I’ve built into one or two of my applications to
allow administrator users to set program options without having to get into the
code. This table has a field for each option but only one record. Whatever
maintenance section I’ve built into the program contains an options screen that
updates the values in this table but does not allow the addition of extra records.
The option values are referenced by the program as needed with lookup functions.
Remember that all users should be able to read from this table, or the query based
on it, but the only users that should have any form of write-access to it are the
administrator(s).
Setting it up ...
(NOTE: This function requires user-level security to be implemented.
The system uses the user name to allow the administrator in for maintenance purposes
while excluding all other users. Without individual logins, all users show
up as "Admin".)
My first step was to add a boolean (True / False) field entitled “Maintenance” to
the Options table. This field can be used by the administrator to signal that
the database is being worked on by setting it to True. The field should be
added to the program’s options screen where the administrator can turn “maintenance
mode” on by checking the correct box.
Now that you are able to activate a maintenance option, the next step is to signal
the copies of the front-end being run by the users. What I did was to write
a short function that would check for the activation of maintenance mode.
The function looks like this:
Public Function fnMaint() As Boolean
On Error GoTo fnMaint_Err
Dim blnMaint As Boolean
Dim strExempt As String
blnMaint = DLookup("[AdminOptions]![Maintenance]", "AdminOptions")
strExempt = Nz(DLookup("[AdminOptions]![Exemption]", "AdminOptions"), "Admin")
If blnMaint = True And strExempt <> CurrentUser() Then
fnMaint = True
Else
fnMaint = False
End If
Exit Function
fnMaint_Err:
MsgBox Err.Number & " - " & Err.Description
End Function
A function is created and set to return a boolean (True / False) result.
Two variables are initialized, one string and one boolean The program
does a lookup on the Maintenance field and assigns the value to the blnMaint variable.
A second lookup is performed on the Exemption field and the result is assigned to
the strUser variable. If the Maintenance field is set to True AND the name
of current user does NOT match the Exemption field, the function returns a True
value indicating Maintenance mode is active, otherwise the function returns a value
of False.
Note that in addition to checking the value of the Maintenance field, this function
also checks the Exemption field for the user name of the administrator who activated
Maintenance Mode. This is important because, as the administrator, you don’t
want your copy of the front-end to keep shutting down while you are trying to make
the changes. The options screen that I use to set the values in the Options
table is also programmed to automatically insert the administrator's name into the
table when Maintenance Mode is activated.
The program now has a simple method to check for maintenance mode:
If fnMaint() = True Then ...
The program needs to check for this at regular and appropriate checkpoints throughout
its operation. Some of these would include:
Selection of menu options
At startup
Selection of command buttons on forms
CurrentRecord events on forms and subforms
The more thorough you are in designating checkpoints, the better this method will
work. If you forget to include a checkpoint, it could allow users to stay
in the program indefinitely. On the other hand, you should also be careful
to only insert the checkpoints at places where any changes to open records have
been saved. The above examples are a good starting point.
It’s also a fact that users will be distracted by other things and leave programs
open unattended for long periods of time. This can be not only annoying for
you as the administrator but can also affect data security in programs that contain
sensitive or confidential information. You can program forms within your application
to close themselves, as appropriate, after a given interval of inactivity.
For more information on this, refer to Microsoft's support site,
http://support.microsoft.com and search
for the following article:
Detect User Idle Time or Inactivity in Access 2000 (Q210297)
Now your program needs to know what to do when it finds that maintenance mode has
been turned on.
I designed a small form which contains a text box notifying the
user that the database has entered maintenance mode and is about to be shutdown.
The Timer value on this form is set to around 5 seconds and the OnTimer event saves
everything and shuts down the application. When designing this form, be sure
to set the Modal property to True so that it does not allow any other action while
the form is open and remove any means of closing the form so the smart user won’t
close it and stop the process. The form is opened by a procedure you write
that looks something like this:
Public Sub procMaint()
If fnMaint = True Then
DoCmd.OpenForm "Shutdown"
End If
End Sub
Thus, each checkpoint that you designate within the program only needs to call the
procMaint procedure shown above like so:
Call procMaint
If maintenance mode is in effect, the form will open and the application will close
after the form timer runs out. If the mode is not in effect, procMaint will
end without doing anything and the program will proceed with the action the user
has selected.
A variation on this method that would allow you to take the system down faster would
be to have a form open in the background at all times while the program is running.
The timer on this form could be set to an interval chosen by the administrator and
the OnTimer event would simply check for a fnMaint value of True. Finding
a True value, the program would then close according to whatever procedure the programmer
designed. If this variation is to be used to allow for daily maintenance or
backups, it's especially important to provide a warning allowing users to save their
work prior to shutdown or, at the very least, use the Access functions to save all
records.
Once this type of maintenance mode is implemented, it should be used sparingly but
it does come in handy when it’s really needed. Once any maintenance is finished,
the administrator should also remember to turn maintenance mode off so that the
users can access the system again.
For amusement and bonus points, your next challenge could be to design a feature
that plays a sound effect on your machine whenever a remote user voluntarily or
involuntarily exits the program … perhaps the sound of a door slamming shut or a
car screeching to a halt. On widely distributed programs, however, the effect
of one of these sounds playing in repeated and rapid succession within moments after
maintenance mode is activated may provide just a little too much amusement (read:
temptation) for a particularly bored administrator.
For more information on operations with Microsoft Access, you can consult Microsoft's
searchable online support section at
http://support.microsoft.com. If you're
comfortable with Usenet, the newsgroup
comp.databases.ms-access is also an
excellent resource. The complete newsgroup archive is available online at http://groups.google.com.