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:
- 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);
- 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.
- 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.
- Then we need to create a form for the administrator to use to review the data.
Disclaimer/Notes:
If you do not have Microsoft Access, simply download and install the freely available runtime version (this permits running MS Access databases, but not modifying their design):Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime
In no event will Devhut.net or CARDA Consultants Inc. be liable to the client/end-user or any third party for any damages, including any lost profits, lost savings or other incidental, consequential or special damages arising out of the operation of or inability to operate the software which CARDA Consultants Inc. has provided, even if CARDA Consultants Inc. has been advised of the possibility of such damages.
Download a Demo Database
Feel free to download a 100% unlocked demo copy by using the link provided below:
Download “Access - Who's Logged In (x32 accdb)” WhosLoggedIn_V1.000.zip – Downloaded 52196 times – 33.27 KBVersion History
V1.000 (2018-10-18)
