Another common need for most database administrators & developers is to be able to see who is logged into a database. A very common solution to this in the past has been to use the Jet UserRoster, see:

• How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access
• Who’s Logged On / Who’s Connected
• Miscellaneous Library (see the heading WhosOn97)

I personally find much more useful to create a table to log the database usage by the users and have a form open in hidden mode at the database startup to populate the table in question. There is also the added benefit that this method permit much more detailed information, and historical review (not possible with the UserRoster). The historical data can be very useful when determining users that did not disconnect properly (which can lead to corruption) from the database. The extra information can also be useful when troubleshooting problems.

So how can you create this simple log tracking form? Very easily and here is how:

1. First, we need to create a table to log the comings and goings within our database. Please note that you can add or remove the quantity (number of fields) you wish to keep track of. To do so you can use the following Data Definition Language (DDL) to create the corresponding MS Access table:

CREATE TABLE tbl_Db_UserTracking 
   (UTEntryID AUTOINCREMENT PRIMARY KEY,
   OSUserName VARCHAR(255) NOT NULL,
   ComputerName VARCHAR(255),
   ComputerIP VARCHAR(20),
   DbEntry datetime NOT NULL,
   DbExit datetime);

2. Then we need to create a login tracking form which will get open at startup in a hidden mode (so the user never actually sees it). Below is one example of this form. Since the form is never actually visible, you need not waste any time formatting it or performing any special layout… A bare minimum is required.
3. Then we need to setup a method to automatically launch the form at startup. Since all of my databases use an AutoExec macro to perform a number of steps (relink the table, check user permissions, stratup logoff forms, …) at starup it is only logical to merely add this to the function that the AutoExec macro calls.
4. Then we need to create a form for the administrator to use to review the data.

Disclaimer/Notes:

Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime

Download a Demo Database

Feel free to download a 100% unlocked demo copy by using the link provided below:

Version History

V1.000 (2018-10-18)