There are (at least) 3 solutions to detect when a user locks his/her workstation on Windows and react on the event.
- A light and efficient solution using Classic Visual Basic (here VB5).
- A very bad solution in VBA, which involves subclassing and using the Windows API directly.
- An hybrid solution for an Access (VBA) application, which uses an out of process 32bits ActiveX EXE server written in Classic Visual Basic (VB5), that’ll work with 32 and 64bits VBA versions.
I’m trying not to use C (or any other language/tool) to write a DLL, although that would be also easily possible.
The windows API function that allows us to be notified when a user session is locked or unlocked (among other notifications) is the WTSRegisterSessionNotification() function.
Solution 1: The Classic Visual Basic 5 solution
We generate a simple 32bits standalone EXE application:
Get the binaries:
SessionLockDetector.exe (13.5 K, MD5: 57f23cb31c6f9ccd2a35c52384b90ff5)
If you need the VB5 runtime DLL (should already be present on XP up), download it here, unzip it and drop it in the same directory as the SessionLockDetector.EXE (.zip MD5: 7f32b01c04b0208ac917d7e266eb6709, extracted .dll MD5: eac679185ad621eeace9b6b286372f27).
Get the source code: SessionLockDetector (standalone) Visual Basic 5 project.
This task in quite easy in VB5 and it is also nicely stable. We just have to subclass the Form to be able to receive and handle the windows messages sent by the system.
Solution 2: The (very) bad solution
You might be tempted, as I did, to simply jump into a new Access database and apply the same technique. Just add the standard and class modules from the vb5 project, and subclass an Access Form like we did for the VB5 Form.
But Access is a different beast than vb5.
Subclassing the Form (in VBA) will make Access quite irresponsive at some point, because the subclassed form will throw a lot of messages in the WindowProc hooked into the (single threaded) VBA interpreter. Access will enter an almost unbreakable loop trying to keep up handling the event cascade while also trying to do its own work (you will see the VBA IDE window title bar endlessly update its text, if you try to run the sample database; but don’t do it please, take my word for it). At this point, even Windows itself (the desktop explorer process) will become frighteningly irresponsive. So take at look at this database code if you’re curious, but, once again, DO NOT EXECUTE / OPEN the “frmMain” form it contains (open in design mode only, don’t double-click it).
You’ve been warned. Download the very bad database that you shouldn’t open, at your own risk.
Solution 3: The hybrid solution
This is where we build an ActiveX EXE server with the same VB5 code that we used in the first solution, but we wrap the functionality inside an automation class. This will be the “LockUnlockDetector” class (which fully qualified with the name of the ActiveX server is “SessionLockDetector.LockUnlockDetector”), that will throw just two events, one for when the session was locked (OnSessionLock), and the other when it unlocks (OnSessionUnlock).
Building the VB5 ActiveX EXE server
Here’s the code of the LockUnlockDetector class module (VB5, 32bits):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
Option Explicit Event OnSessionLock() Event OnSessionUnlock() Private mfrmMain As frmMain Friend Sub NotifySessionLock() On Error Resume Next RaiseEvent OnSessionLock End Sub Friend Sub NotifySessionUnlock() On Error Resume Next RaiseEvent OnSessionUnlock End Sub Public Function StartMonitoring() As Boolean On Error Resume Next Set mfrmMain = New frmMain Set mfrmMain.DetectorObject = Me StartMonitoring = mfrmMain.StartSubclassing() End Function Public Sub EndMonitoring() On Error Resume Next mfrmMain.StopSubclassing Set mfrmMain.DetectorObject = Nothing Unload mfrmMain Set mfrmMain = Nothing End Sub |
By the way, according to the coding guidelines published here, we’re creating a class which will be made available outside of the VB project (that’s the goal), so we do not use any prefix before the class name (no “C” preceding the name).
To create an ActiveX EXE server, we set the properties of the project like that:
Now every object instance of class LockUnlockDetector will run in its own thread, and as this thread is in the EXE, it runs in the background and isolates the host (Access) from any problem occurring in the EXE.
Best of all, even if the EXE is 32bits, thanks to the beautiful architecture of COM (ie. ActiveX), cross platform calls are transparently marshalled and a 64bits Access VBA host can also use these 32bits automation classes.
The only disadvantage of this solution, is that we have to register the EXE server on the target machine.
In order to do that, we open an console window in administrator mode, navigate to the directory where the compiled EXE server is, and run it. This will silently write the required windows registry entries, and we’ll see the automation server appear in the Project References dialog of the VBA IDE in Access.
To use the detector class in Access, we then add the reference to our project:
Then we create a form (named frmMain here), and we just need this code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Private WithEvents moDetector As SessionLockDetector.LockUnlockDetector Private Sub Form_Load() Set moDetector = New SessionLockDetector.LockUnlockDetector If Not moDetector.StartMonitoring() Then MsgBox "Start monitoring failed", vbCritical End If End Sub Private Sub Form_Unload(Cancel As Integer) moDetector.EndMonitoring Set moDetector = Nothing End Sub |
Then we can respond to Lock/Unlock events as we wish:
|
1 2 3 4 5 6 7 8 9 10 |
Private Sub moDetector_OnSessionLock() AddHistoryRecord "SESSION LOCK" RefreshView End Sub Private Sub moDetector_OnSessionUnlock() AddHistoryRecord "SESSION UNLOCK" RefreshView End Sub |
In the provided sample Access database I added an history table to store the events, that’s what the AddHistoryRecord function does:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Private Sub AddHistoryRecord(ByVal psEventText As String) Const LOCAL_ERR_CTX As String = "AddHistoryRecord" On Error GoTo AddHistoryRecord_Err Dim SQL As String SQL = "INSERT INTO SessionLockHistory(EventDate, EventText) VALUES (" SQL = SQL & AccessSQLDateTime(Now) & ", '" & Replace(psEventText, "'", "''") & "')" CurrentProject.Connection.Execute SQL AddHistoryRecord_Exit: Exit Sub AddHistoryRecord_Err: ShowError LOCAL_ERR_CTX, Err.Number, Err.Description Resume AddHistoryRecord_Exit End Sub |
Downloads
AxSessionLockDetector.EXE – ActiveX EXE server binary (if you’re missing MSVBVM50.dll see the above download link in solution 1).
SessionLockDetector – VB5 ActiveX EXE server project and source code.
SessionLockDetector.accdb sample database using the EXE server (remember to register the SessionLockDetector ActiveX server (AxSessionLockDetector.exe) binary by running it once with administrator privileges).
Then
- You can extend the functionality of the ActiveX EXE server to be notified for other events supported by the WTSRegisterSessionNotification windows API, and raise appropriate events. You can write a windows service that directly uses the server or transform solution 1 to be a windows service.
- You can also modify solution 1 to be notified for other supported events and take any action you need, like running a batch file.
- You can use the hybrid solution in your Access projects to be able to do smart things like freeing database or resource locks once a user locks its workstation.
MIT Licence
Copyright © 2019, Francesco Foti, devinfo.net.
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. The Software is provided “as is”, without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fitness for a particular purpose and noninfringement. In no event shall the authors or copyright holders (Francesco Foti) be liable for any claim, damages or other liability, whether in an action of contract, tort or otherwise, arising from, out of or in connection with the software or the use or other dealings in the Software. Except as contained in this notice, the name of Francesco Foti or devinfo.net shall not be used in advertising or otherwise to promote the sale, use or other dealings in this Software without prior written authorization from Francesco Foti.
Recent Comments