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 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:

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.

DataWright Information Services

Consulting and Resources for Excel and Access