Finding out who is using your database
If you have multiple users in your database, you often need to
know who is in the database when it comes time to do some
maintenance. If you have set up user-level security, it’s possible
to get that information with the Jet User Roster, which reads the
contents of the .ldb locking file for the database. However, if you
are not using user-level security, you can only retrieve the machine
name.
This method is an alternative that will allow you to log your
users by their Windows login, see who is currently in the database,
and keep a record of when users were using your system. The system
consists of one table, two simple forms, and a module with two
custom functions.
How it works
A hidden form called frmMonitor is opened in the background when
the database launches. At this stage it triggers a custom function,
LogOn(), which creates a new record in tblUserLog, recording the
user’s Windows login name and the current date and time. When the
user closes the database, frmMonitor triggers LogOff() which writes
the current time to the LogOff field in tblUserLog.
To see who is in the database, we use a form with a listbox that
displays all log records with a blank LogOff field. Another button
on the form lets you see the full history, sorted descending by
login date/time.
- You can download a sample database
here
(17,616 bytes)
The components
tblUserLog
This table goes in the back end database of your split system. It
has four fields, as shown below:
Field | Data Type | Comments |
---|---|---|
UserLogID | Autonumber | Primary Key |
UserID | Text | User’s Windows login name |
LogOn | Date/Time | Login timestamp; default value is =Now() |
LogOff | Date/Time | Logout timestamp |
modUserLog
This module holds the two custom functions that write the log
table values. Both functions use Environ(“username”) to find the
Windows login of the current user. They also use DoCmd.SetWarnings
to suppress the dialog that “you are about to append / change /
delete xxx records”, and to reset those warnings once the operation
is complete. The code for these functions is shown below:
Function LogOn() Dim sUser As String Dim sSQL As String DoCmd.SetWarnings False sUser = Environ("username") sSQL = "INSERT INTO tblUserLog ( UserID )" _ & "SELECT '" & sUser & "' AS [User];" DoCmd.RunSQL sSQL DoCmd.SetWarnings True End Function
The SQL statement in LogOn() is an Append query. It writes the
Windows login to the UserID field; because that login value is also
the result of a function, it is given the alias [User] so that
Access can treat it like a field.
The DoCmd.RunSQL command runs the query, creating the new log
record.
Function LogOff() Dim sUser As String Dim sSQL As String DoCmd.SetWarnings False sUser = Environ("username") sSQL = "UPDATE tblUserLog SET tblUserLog.LogOff = Now() " _ & "WHERE tblUserLog.UserID='" & sUser & "' AND tblUserLog.LogOff Is Null;" DoCmd.RunSQL sSQL DoCmd.SetWarnings True End Function
In this case, sSQL is an Update query. The WHERE clause filters
the log to the correct record by using the
Windows login name and selecting null LogOff fields.
frmMonitor
frmMonitor is opened when the database opens, and stays in the
background until the database closes. It contains two very simple
event procedures:
Private Sub Form_Load() modUserLog.LogOn End Sub Private Sub Form_Unload(Cancel As Integer) modUserLog.LogOff End Sub
Form_Load is triggered at database launch time, and runs the
LogOn() function. Form_Unload is triggered when the database (and
the form) is closed, and it runs the LogOff() function.
frmUserLog
The listbox has the following properties set:
Property | Value / Setting |
---|---|
Column Count | 3 |
Column Widths | 3cm;3.85cm;3.85cm |
Width | 10.7cm |
Column Heads | Yes |
Row Source | SELECT tblUserLog.UserID, tblUserLog.LogOn, tblUserLog.LogOff FROM tblUserLog WHERE (((tblUserLog.LogOff) Is Null)) ORDER BY tblUserLog.LogOn DESC; |
The code for the form is listed below.
Option Compare Database Option Explicit Const sSELECT = "SELECT tblUserLog.UserID, tblUserLog.LogOn, tblUserLog.LogOff " _ & "FROM tblUserLog " Const sWHERE = "WHERE (((tblUserLog.LogOff) Is Null)) " Const sORDER = "ORDER BY tblUserLog.LogOn DESC;" Dim sSQL As String Private Sub cmdAll_Click() sSQL = sSELECT & sORDER With Me.lstUsers .RowSource = sSQL .Requery End With Me.lblUsers.Caption = "Full Log" End Sub Private Sub cmdClose_Click() DoCmd.Close acForm, Me.Name End Sub Private Sub cmdCurrent_Click() sSQL = sSELECT & sWHERE & sORDER With Me.lstUsers .RowSource = sSQL .Requery End With Me.lblUsers.Caption = "Currently Logged On" End Sub
By default, the form will display users who are currently logged
on. The All button will display all user logins, so that you can see
the history, and the Current button will switch back again. In
addition, each button rewrites the caption for the listbox to give
user feedback.
The code is modular. The SQL statement has been split up into
three components, and placed in the Declarations section of the
module as the constants sSELECT, sWHERE and sORDER.
To display all users, the Rowsource of the listbox is changed to
sSELECT & sORDER. To show only current users, sWHERE is inserted
into the listbox SQL. The Requery command refreshes the listbox
display.
Using the system
To use this in your setup, do the following:
- Import the table, forms and module into your database.
- Create a command button that launches frmUserLog. If you
want all users to be able to use this feature, place the button
/ launch command on the main menu. You can place this on a form
that only administrators can get to, if you need to restrict
access to this view.