Access Database Projects - User Management Example
This will be the first in a series of articles dealing with running an access
data project in a multi-user enterprise environment.
An access data project (.adp file) differs from a regular access database (.mdb
file) in many ways. It also eliminates tons of the problems you are likely to
encounter when running a database that requires several users to be logged in at
the same time. The main difference between the two is that an MDB file contains
all the code, data, queries etc... in one neat little container, the ADP
separates the data from the application by using tables and queries residing on
the SQL server. The main advantage of the ADP over the MDB is that the data is
housed in a central place. My big qualm with the MDB is that any idiot end user
can make a copy and put new data in that copy, thus creating two of the same
database with different data in it (developer nightmare). Then we have multiple
databases out there with each user adding different data to each one, they make
copies of their new ones and distribute them to everybody, now we have 20
different databases, none of them have all the data we would like to have.
Hopefully you see where I am going with this. With an ADP, users can make copies
of the project all day long if they want, and as developers, we aren't sweatin
it because we know there is only one possible spot for them to change data. The
only reason I am bothering to explain all this is because the following example
isn't very practical for your run of the mill MDB.
So you have an access project all set up and ready to go, but the built in
security in access is not going to cut it for the sensitive information
contained within your SQL server. Additionally, you want to have a multi-user
login, authenticated against a table in your database instead of everyone using
one password. Although this concept isn't very hard to implement, things get
fuzzy when you have people who don't have their own id's in the database using
someone else's id to get in. The best way to do this is by allowing only one
instance of the database to be open per user. The problem with this arises when
you leave a copy running in another room and don't want to walk ALL THE WAY over
there to close it down before you log in somewhere else (lazy people make the
best programmers). The obvious solution is to make the copy you are opening
detect that you are logged in elsewhere and close the instance that you are
logged in to. To accomplish this you need several things:
1) Means for keeping track of currently logged in users
2) A login function that detects if you are logged in before it logs you in
3) A method of sending a logoff request to the remote program
4) A listener in the program to shut it down when the logoff request is sent
5) A method of waiting for the remote logoff to complete before the new login
can be processed
To follow this from here on you are going to need:
-A copy of my example database
MultiUserEnv.mdb for Access 2000 / XP(176kb) (save
this file to disk and make sure the file is not read only before you open it)
-A network of at least two computers, each with access 2000
-A share on one of the computers so that both can access the same database at
the same time
(Keep in mind that this is only an EXAMPLE, this is dummied down from a real
database that I am currently implementing with an access .adp project and a SQL
server. I would be in a world of pain if I were to find out that some heathens
had taken my masterpiece and foolishly misused it in an mdb)
Now that you have it downloaded, try it out
-Open the database on computer 1
-Log in using admin for both username and password (the database window will
-Open the database on computer 2 using the same uname and pwd
-You will notice a prompt to log your other session out, click yes
-the database open on computer 1 will be shut down and you will be logged in on
Now on to how it is done...
Anyone who is familiar with ASP's Application and Session objects will realize
the beauty of my SetTmp and GetTmp functions (located in mod_Functions). They
basically get and set variables that can be available to either all users or
just the user who created them (mostly the latter). These two functions in
conjunction with tbl_Temp are the backbone of this system. They are used to
write the "IsLoggedIn" and "LogMeOut" values to the database.
"IsLoggedIn" is a timestamp that is performed when the user submits his auth
info and Login() is called.
"LogMeOut" is the request that is sent when the login function detects an "IsLoggedIn"
for the user you are authenticating as.
The Login function therefore takes care of parts 1-3 of what we need to do. Part
4 is taken care of by "frm_Background", a form designed to run in hidden mode as
long as the program is running. Using the timer event of this form we are able
to make a listener that watches for "LogMeOut" to be set. When it sees this, it
will log itself out (by erasing the "IsLoggedIn" record and perform a docmd.quit.
On the newly logging-in side, after "LogMeOut" is set, a waiting form "frm_LoginWait"
is set up similar to "frm_background." The only difference is that it is waiting
for "IsLoggedIn" to be removed by the remote program's "frm_Background" so that
it can create an "IsLoggedIn" of it's own to replace it. Once "IsLoggedIn" is
removed and the remote app shut down, you are now logged in cleanly on another
So there you have it! Make sure to play around with the example database some
more since it has some extra little treasures in there for your multi-user
If this article / example helped you, return the favor and help someone who
could use your help.
Until next time,