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:

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 above three components are needed to write and store the log data. To view it, frmUserLog has a listbox and two command buttons to toggle between currently logged in users and all log records. A third button closes the form.

The listbox has the following properties set:

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.

 
Home | About |
Links |

Contact | Excel |
Access