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) or zipped MultiUserEnv.zip(28kb) (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 become visible)
-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 computer 2

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 computer.

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 amusement.

If this article / example helped you, return the favor and help someone who could use your help.

Until next time,

Joe Korzeniewski