Are You There 4
By Richard Rost
3 days ago
Check Windows for System Level Activity - Part 4 In this lesson, we will discuss a new method for determining if a user is physically present at their computer using a Windows API function. Building on the Are You There series, I will show you how to use an API call in Access to accurately track how long the computer has been idle - detecting keyboard or mouse inactivity even when the user switches to another application. We will walk through setting up a simple get idle seconds function in VBA and discuss how it complements the Access database activity tracking from earlier lessons. MembersThere is no extended cut, but here is the file download: Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, get idle seconds, Windows API, get last input info, get tick count, idle time detection, VBA, track user inactivity, last input info structure, user32, kernel32, auto logout, lock workstation, status box timer, prevent idle, activity tracking, idle mod
Intro In this lesson, we will discuss a new method for determining if a user is physically present at their computer using a Windows API function. Building on the Are You There series, I will show you how to use an API call in Access to accurately track how long the computer has been idle - detecting keyboard or mouse inactivity even when the user switches to another application. We will walk through setting up a simple get idle seconds function in VBA and discuss how it complements the Access database activity tracking from earlier lessons.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost. This is a completely unplanned part 4 of my Are You There series. I was not originally planning to make part 4 in this 3 part series, but several of you pointed out a really useful Windows API function that can tell you whether the user is actually still sitting at their computer.
Parts 1 through 3, if you have not watched those, go watch those first and then come back. Those videos are for tracking activity inside your Access database, and that is still the best way to tell whether the user is still actively working in your application. But what if they minimize Access and then go spend a half an hour answering email, browsing the web, watching some of my videos, or working in Excel or whatever? From Access's perspective, they appear inactive even though they are still sitting right there at the keyboard.
Now, Windows keeps track of the last time the user touched the keyboard or moved the mouse anywhere on the computer. So with a couple of API calls, we can just ask Windows for that information and then find out exactly how many seconds the system has been idle.
Alright, here I am in my TechHelp free template and yes, I am in dark mode because it is at night and I am using my cool dark mode stuff that I built. Now, if you have never used the Windows API call before, do not panic. Programming with API calls is a lot like driving a car. You do not need to understand how every piston and gear works under the hood. You just need to know where the gas pedal is and what it does.
So in this case, all you really need is one simple function called get idle seconds. Now I am going to create a module and I am going to paste in here my code that I have in my notepad. I am going to go down it slowly so all you guys can get a good look at it. Gold members, this will be in the code vault. I am just going to save this real quick. We are going to call this the idle mod because it is tracking idle seconds from Windows and it is basically get idle seconds, the function we are going to use. It returns a long integer, and it returns the number of seconds since the user last touched the keyboard or mouse. That is it. That is real simple. No complicated setup, no forms, no classes, no extra references, just a simple Windows API call.
Again, you do not need to understand what all of this is doing, but I am just going to give you the real quick overview at the top of the module. This thing here creates a structure called last input info. It is just a container that Windows uses to send back information to us. It includes the size of the structure and a timestamp representing the last input event.
Next, we declare two Windows API functions and these are just functions that exist in Windows that we can tell VBA, hey, go use this thing that is out in Windows in this thing called user32 and kernel32. Those are Windows libraries.
Get last input info asks Windows when the last keyboard or mouse activity occurred, and get tick count returns the number of milliseconds that have elapsed since Windows started.
Then we have our get idle seconds function. Let me see if I can scroll down and get this whole thing. If I get rid of some of the blank lines, I can get the whole thing on the screen. Okay, there, I condensed it for you.
Inside the get idle seconds function, we create a variable based on that last input info structure. We tell Windows how large the structure is. Then call get last input info and if the call succeeds, Windows fills in the timestamp of the last user input. Then we call get tick count to get the current system tick count. By subtracting the last input time from the current time, we get the number of milliseconds the computer has been idle. Finally, we divide that by a thousand to convert milliseconds into seconds and then we return the result. If the API call fails for some reason, the function returns negative one so you know something went wrong and you can check for that if you want to.
That is it. Again, you do not need to understand all that to use this get idle seconds.
Now save this, we are going to close it and we are going to go test it. I am going to go in my main menu, we have got my status box here. We are just going to set up a little timer event in the main menu. I am going to set the timer interval to 1000 so it is a one second timer. Normally, we do not do this, we do not want every second because it is going to steal focus every second, but just for testing purposes.
I am going to go in here and I am just going to say status and then idle colon and get idle seconds and sec like that. So every second, it is going to call get idle seconds, it is going to go ask Windows how long it has been idle for and just status it for me. Once every second that I run.
All right, save it. Debug, compile, hopefully everything compiles. Okay, good, we are good. Save it, close it, and then let us open it back up. There you go. You can see it has been idle zero. Now it is counting up because I have my hands off. I have not done anything. If I go touch my mouse, watch what happens. Look at that. It reset back to zero again. See, it knows whether or not I am actually doing something. I am just going to hit the shift key on the keyboard. Look at that. Shift reset it to zero.
I am going to go do something else. Let me open up Notepad. I have got Notepad open, totally separate application. You are sitting over here. I am just going to wait now, let it go idle. Access does not know I am doing it. I have not touched anything. Now I am just going to start typing in Notepad. Look at that. Same, went back to zero. So my Access database now knows when I am doing anything anywhere else in Windows. Pretty cool.
This is a great tool. I would still recommend using the techniques from parts one through three. Continue tracking activity inside your Access database and reset your timer on the form whenever the user does something in your application. That is going to prevent your hidden timer form from constantly stealing focus, especially if you are doing stuff in the VBA editor. You do not want that to happen.
Then as your final check before logging out the user, you could use the get idle seconds. Make that like the last straw. Are you really here? Are you doing something else? Are you surfing? Do they still say surfing the web? Is that still a thing? I know that this came up in the forums a little while ago. I thought that went the way of the information superhighway somewhere around 2003.
This way at least, if Windows says the user has not touched the keyboard or mouse in a long time, you know that they truly stepped away from the computer. Think of this like your last line of defense. The earlier techniques tell you whether they are still using the database. This technique will tell you whether they are still at the computer at all. You could even do other stuff too. If you detect they are not at the computer, you could log them out of their database and then lock the Windows workstation. If you guys want to see how to do that, let me know. That is pretty easy to do too, but that is a whole separate video.
This is probably going to be the last. I said part three was going to be the last, but this is the last part for now, part four. Let me know what you thought. Post a comment down below. Are you going to use this? Do you like it? Do you not like it? Do you want to see more? That is going to do it folks.
That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper my friends. I will see you next time.
If you enjoyed this video, hit that thumbs up button right now and give me a like. Also, be sure to subscribe to my channel which is completely free, and make sure you click that bell icon and select all to receive notifications whenever I post a new video.
If you are new to Microsoft Access, check out my Access Beginner Level One course. It is over four hours long and covers all the basics like tables, queries, forms, and reports. It is a great place to start and it is also completely free.
Members of my channel get extended cut videos, sample databases, access to my code vault, and full training classes every month. Click the join button for details.
Thanks for watching. I am Richard Rost with AccessLearningZone.com. Live long and prosper my friends. I will see you next time.Quiz Q1. What is the main purpose of the Windows API function discussed in this video?
A. To track the user's keyboard and mouse activity at the Windows level
B. To print reports from Access
C. To create new user accounts in Windows
D. To optimize database performance
Q2. How does Access normally detect if a user is active in your application?
A. By checking if the user is connected to the internet
B. By tracking activity only within the Access database
C. By monitoring system processes
D. By sending network requests
Q3. What limitation does only tracking activity inside Access applications present?
A. It cannot detect if users are using the VBA editor
B. It cannot tell if a user is still at the computer but working in another application
C. It only works on Windows 11
D. It cannot save database files
Q4. Which structure is used to access the last input information from Windows?
A. LASTINPUTINFO
B. INPUTSTRUCT
C. STRUCTINFO
D. MOUSEKEYSTRUCT
Q5. What does the get idle seconds function return when the API call fails?
A. 0
B. 1000
C. -1
D. Null
Q6. Which API functions are declared to interact with Windows in this approach?
A. GetLastInputInfo and GetTickCount
B. GetUserInput and GetTimeIdle
C. GetActivity and GetSecondsIdle
D. WinActiveUser and WinTimeElapsed
Q7. Why does the function divide the difference in milliseconds by one thousand?
A. To convert seconds into minutes
B. To convert milliseconds into seconds
C. To calculate hours of inactivity
D. To format the output for a report
Q8. What is the recommended way to use the get idle seconds function in an Access form for testing purposes?
A. Set the form's timer interval to 1 second
B. Only check once per session
C. Attach it to a query
D. Use it only in reports
Q9. According to the video, what should be your primary method for tracking user activity in Access databases?
A. Use the get idle seconds API as the only method
B. Track activity inside Access and use get idle seconds as a final check
C. Only monitor mouse movements in Windows
D. Ignore user activity altogether
Q10. What is a possible action if the system detects a user has been idle for too long, according to suggestions in the video?
A. Automatically print a report
B. Send an email reminder
C. Log the user out and lock the Windows workstation
D. Restart the computer
Answers: 1-A; 2-B; 3-B; 4-A; 5-C; 6-A; 7-B; 8-A; 9-B; 10-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on part four of my "Are You There" series for Microsoft Access. Originally, this was intended to be a three-part series, but after hearing from many of you, I decided to add a fourth part because of a useful Windows API function that makes it possible to detect if a user is still present at their computer.
If you have not watched parts one through three, I recommend doing that first. Those videos demonstrate how to track activity inside your Access database itself, which is still the most reliable way to see if a user is actively using your application. However, those methods only detect activity within Access. If a user minimizes your database and spends time working in another application like Excel or even just browsing the web, Access thinks they are inactive even though they might be right there at the keyboard.
Windows itself actually keeps track of the last time the user pressed a key or moved the mouse, anywhere on the computer. By using a couple of simple API calls, we can get that information and see exactly how many seconds the system has been idle, regardless of which application the user is in.
For this example, I am working within my standard TechHelp free template in dark mode. If you have never used a Windows API call before, do not worry. It is a lot like driving a car. You do not need to understand every detail under the hood - just know how to use the functions you need.
The function I am introducing today is called get idle seconds. This is all you need. You create a new module - let's call it idle mod since it tracks idle seconds from Windows. The main function returns a long integer that represents the number of seconds since the last keyboard or mouse input. There is no complex setup involved. You do not need special forms, classes, or references. It is just a straightforward API call.
You do not have to understand the technical details, but I will quickly summarize what is happening at the top of the module. First, we define a structure called last input info. It is a container Windows uses to send the relevant timestamp back to us. The size of the structure and the timestamp are included.
Next, we declare two Windows API functions. These live in the Windows libraries user32 and kernel32, and VBA simply uses them as external functions. The first tells us when the last keyboard or mouse activity happened, and the second returns the number of milliseconds since Windows started.
The get idle seconds function creates a variable based on the last input info structure and tells Windows how large it is. By calling the relevant API function, Windows fills in the timestamp for the last user input. We then get the current system tick count, subtract the last input time, and that gives us the number of milliseconds the computer has been idle. Dividing by a thousand converts this to seconds, which is what the function returns. If something goes wrong with the API call, the function returns negative one as an error signal.
You do not need to fully understand all of that in order to use get idle seconds in your projects.
Once this module is saved, I set up a simple demonstration. In my main menu form, I have a status box. I configured a timer event for this form, set with a one second interval for demonstration. Normally, you would not refresh every single second in production because it could become intrusive, but it is useful here for testing.
With each tick of the timer, the status box displays the number of seconds since the last user input, based on the value returned by get idle seconds. When I open the form and take my hands off the keyboard and mouse, you see the idle seconds begin counting up. As soon as I move the mouse or press a key, it immediately resets to zero, showing that Windows knows exactly when input occurred - even in another application like Notepad.
What this means is that your Access database can now know when someone is interacting with any part of Windows, not just your database. This opens up new possibilities for tracking true user presence.
I still recommend using the activity tracking techniques from parts one through three to monitor activity within your database itself. Resetting your own application-specific activity timer whenever the user does something in Access avoids problems like focus-stealing from background timer forms. This Windows API method should be the final check before deciding to log users out - think of it as your last line of defense. If Windows says nobody has touched the keyboard or mouse in a certain amount of time, you know the person has likely stepped away from the computer completely.
You could even go further, for example, automatically logging users out of your database and locking the Windows workstation if you detect inactivity. If you want to learn more about that kind of functionality, let me know and I can cover it in a future video.
That wraps up this surprise fourth part of the series. I would love to hear your feedback - let me know if you find this helpful or if there is more you would like to learn about this topic.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Using Windows API to check system idle time
Creating the get idle seconds VBA function
Declaring and using the LASTINPUTINFO structure
Calling GetLastInputInfo and GetTickCount APIs in VBA
Calculating user idle time in seconds in VBA
Handling API call failures in the idle function
Setting up a timer event in an Access form for idle checks
Displaying idle time in an Access form status box
Testing idle detection across different applications
Integrating Windows-level idle checking with Access activity trackingArticle If you are building an Access database application and want to detect if a user is still actually present at their computer, not just whether they are clicking around inside Access, there is a straightforward way to do this with a Windows API call. In earlier methods, you might be tracking user activity within Access itself by monitoring form events or interactions. However, those techniques only tell you if someone is using your database. If the user opens another program - maybe email, Excel, or their web browser - Access will consider them idle, even if they are still there, actively working elsewhere on their computer.
Windows itself keeps track of the last time the mouse or keyboard was used anywhere in the system. With just a couple of API calls, you can access this information and calculate exactly how long the computer has been idle. In practical terms, you can find out how many seconds it has been since the user last interacted with their keyboard or mouse - no matter which application was active.
To implement this in Access, start by creating a new module. You do not need to understand all the internals of how API calls work; you only need to know how to make use of them. Here is the full VBA code you will add to your module:
' Paste this into a new standard module, such as Module1 or "IdleMod"
Option Compare Database
Option Explicit
Private Type LASTINPUTINFO
cbSize As Long
dwTime As Long
End Type
Private Declare PtrSafe Function GetLastInputInfo Lib "user32" (ByRef plii As LASTINPUTINFO) As Long
Private Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
Public Function GetIdleSeconds() As Long
Dim lii As LASTINPUTINFO
Dim lngTickCount As Long
Dim lngLastInput As Long
lii.cbSize = Len(lii)
If GetLastInputInfo(lii) Then
lngTickCount = GetTickCount()
lngLastInput = lii.dwTime
GetIdleSeconds = (lngTickCount - lngLastInput) \ 1000
Else
GetIdleSeconds = -1 ' API call failed
End If
End Function
This code does a few important things. First, it defines a structure - LASTINPUTINFO - that Windows uses to provide information about the last input event. It then declares two Windows API functions: GetLastInputInfo, which retrieves the time of the last input, and GetTickCount, which returns the system uptime in milliseconds. The GetIdleSeconds function works by populating the structure, calling the API, and if successful, calculating how many seconds have elapsed since the last input.
You can test this by displaying the output on a form. For example, suppose you have a status box on your main menu form in Access and you want to display the idle time there. Set the Timer Interval of the form to 1000 (so it updates every second for testing), and in the form's On Timer event, add code like this:
Me.StatusBox = "Idle: " & GetIdleSeconds() & " sec"
With this setup, every second, Access asks Windows how long it has been since the user last touched the keyboard or mouse anywhere in the system. If you stop moving the mouse and do not press any keys, the number increases. The moment you interact with the computer - whether by touching the keyboard or even clicking in a different application - the value resets to zero.
This approach can be helpful when you want to log a user out or take some other action when they have truly become inactive, not just stopped using your database. However, you should continue to track activity within Access using your regular methods (like checking for form events or changes), especially since frequent timer events can interfere with what you are doing in Access, such as in the VBA editor. Use this Windows idle check as your last line of defense, perhaps right before performing a forced logout, to make certain the person really is away from their computer.
If the GetIdleSeconds call ever returns -1, it means something went wrong with the Windows API call, and you should add some error handling if you want to be extra cautious.
By combining these techniques, you can effectively monitor whether a user is working in your Access application and whether they are at their workstation at all. If you want to go further, such as automatically locking the workstation or logging off the user, that is also possible with Windows API calls.
This method makes your Access applications smarter by ensuring you only act when the user is truly away, whether they have minimized Access or are working elsewhere on their PC. Try adding this code to your own applications and see how it works for your needs. |
