Are You There
By Richard Rost
4 days ago
Auto-Logout Users After Inactivity in MS Access In today's video, we discuss how to automatically log out users from a Microsoft Access database after a period of inactivity. I explain how to create a hidden form with a countdown timer, use status updates to track user activity, and set up the basics for warning users before Access closes itself if no action is taken. We walk through the initial setup steps and review the required VBA programming, as well as the prerequisite tools and functions needed for this process. Holly from San Jose, California (a Platinum Member) asks: How can I automatically log users out of my Access database if they walk away from their desk or leave for the night? Sometimes the database is still open when I need to run a backup or push an update, and to make matters worse, sensitive customer information is left sitting on the screen for anyone walking by to see. I'd like Access to warn them and close itself if nobody responds. 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 CoursesUp Next
Keywords TechHelp Access, automatically log out users, inactivity timer, VBA programming, MessageBox Timer Form, Status Box, IsFormLoaded function, Active Form, Active Control, hidden form timer, reset form timer, DoCmd.OpenForm, TimerInterval, log user activity
Subscribe to Are You There
Get notifications when this page is updated
Intro In today's video, we discuss how to automatically log out users from a Microsoft Access database after a period of inactivity. I explain how to create a hidden form with a countdown timer, use status updates to track user activity, and set up the basics for warning users before Access closes itself if no action is taken. We walk through the initial setup steps and review the required VBA programming, as well as the prerequisite tools and functions needed for this process.Transcript Have you ever had to walk across a building just to log someone out of an Access database so you could run a backup or install an update? Or they left for the day without logging out and then your backup failed because the file was still open?
Welcome to another TechHelp video brought to you by accesslearningzone.com. I'm your instructor, Richard Rost.
Today we're going to talk about automatically logging out users of your Microsoft Access database after they've been inactive for a certain amount of time. We'll give them an "Are you there?" warning and then they'll have a chance to stay logged in before Access closes itself. If they're there, they click the "Still Here" button, and if they're not there, Access shuts off.
Today's question comes from Holly in San Jose, California, one of my Platinum members. She says, "How can I automatically log users out of my Access database if they walk away from their desk or leave for the night? Sometimes the database is still open when I need to run a backup or push an update, and to make matters worse, sensitive customer information is left sitting on the screen for anyone walking by to see. I'd like Access to warn them and close itself if nobody responds."
Well, Holly, for the last part there for the sensitive information, I would enforce making sure that your users have a screen saver like a Windows screen saver so that if there's no activity at all for 10 or 15 minutes, at least their Windows workstation locks. But that will still leave Access open in the background. So in this video, I'm going to show you how to have Access itself shut down if there's no activity for whatever period you specify: half hour, hour, whatever.
Now before we get into it, this is a developer-level video. What does that mean? We're going to need some VBA programming. If you've never done any VBA programming before, this is the place to start. It'll teach you everything you need to know in about 20 minutes to get started.
You will also need my MessageBox Timer Form. If you haven't watched this one yet, this lets you open up a message box with a Yes or No option on it with a timer that you can set. So this timer will start at 60 seconds or whatever you want it to be. Once this pops up in the user's face, they then have 60 seconds to say, "Yes, I'm still here." If not, this will return a No and then your database will close.
So this is definitely a prerequisite. Go watch this video first.
And you'll also need my Status Box. This is part of my TechHelp free template. Essentially, the status box is every time you want to, you can have a message appear in this box on the main menu. I have this appear all the time when a customer opens a particular form or does something or needs something and you don't want it to pop up a message box, you can put it in the Status Box.
This is one way that we can determine that the user is still active. Every time they do something like open a customer form or open an order, you can pop a little message in the Status Box that says, "Customer Form Opened" or "Order 15 Opened." That kind of stuff. That's one way we can track their activity.
Go watch this video too.
We're going to use my IsFormLoaded function to determine if a form is open or not. The second way we can track what they're doing is what form and what control they are on. So go watch my Active Form video and my Active Control, because if they're still sitting on the same active form and active control they were an hour ago, chances are they might be gone. I mean, it's possible they're still sitting in the same field, maybe they're typing in a real long note field, but I would say at least that would be a good opportunity to pop up that message and say, "Hey, you still there? Are you still working on the same field for an hour?"
So go watch all those videos. Those are all free videos. They're on my YouTube channel. They're on my website. Go watch those and then come on back.
Here I am in my TechHelp free template. This is a free database. You can grab a copy off my website if you want to, but you could put this stuff in any database.
The first thing we're going to do is create a hidden form that has our countdown timer in it. You could set the countdown timer to whatever you want. I like an hour myself. An hour is good; if I leave and forget my database is still open, one hour is about a good enough time. You don't want it to be too often. You could check every second. You could check once a minute. Remember, every time you check if the user's doing something, even a hidden form might still steal focus. Any of you who have done any VBA work in the VBA editor know that when a form is open that has a timer running, it steals focus. So we want to minimize that as much as possible. That's why I say set the timer to something really high, half an hour or an hour before it tries to steal focus and do its check.
Let's build that form first. I'm just going to copy my single form, copy, paste. I'm going to call this my "Are You There" checker. I just call the "Are You There" form "Are You There F."
This guy is going to run hidden in the background. So let's go into Design View. We can get the fields off. Actually, I'm going to leave… Yeah, let's take the fields off it. We don't need anything on this form.
I'm going to put a label on here just for you guys to remind you that this is the form with the hidden timer event to check inactivity. You can hide this later and we're going to make it visible for now just so we can see what it's doing and that it's working. We're going to save it and close it.
Now in your main menu or whatever your startup routine is for your database, make sure you open this form. I'm going to go into my main menu. I'm going to go into my OnLoad event that I know I already have in here. Now I've got an event in there that loads the app window position. It just moves my copy of the database into the spot on my screen where I like to record my videos. But we're going to put in here a DoCmd.OpenForm "AreYouThereF." Normally, you're going to come over here and go acHidden so that window starts up hidden.
Why hidden? You don't want your users closing it because if that thing closes, then it defeats the purpose. Of course, you've got your database locked down, right? You follow my simple security video. We don't want users poking around in the navigation pane and opening and closing stuff that they shouldn't be or going directly into your table. Go watch this.
But I am going to rem this out for now and we're going to open it just for development purposes. We're going to open it normally just so we can see it. Slide this form later so that your users don't poke around in it.
So save it. Close it. Now when you open up this form... Where is it? It's back there. Remember, the OnLoad event actually runs before this form is drawn on the screen. So whatever happens in there happens before this guy gets drawn. That form was actually opened and then this one opened on top of it. It's kind of weird.
Move it down here. Save it. That way you don't have that problem again. If I close this and I close this and we open it up, we're good. It's in place. It's going to be hidden later anyway. You can turn off the record selectors and the navigation buttons, all that good stuff to format it however you want. It doesn't matter. You're not going to see it.
Unfortunately, putting a countdown timer on here like we did with the message box timer is going to defeat the purpose, because A. you're not going to see it, and B. if this did have a one-second timer on it, it's going to constantly keep stealing focus. We don't want that. So this is going to have a really long timer in it.
You can set it manually here, but I don't like doing that. I don't like trusting this. I put my timer settings in my code. We're not going to put it in the code. We're not going to put it in the OnLoad event for this form.
One of the ways that we're going to check for activity is using our status function, so I'm going to put this in the status function. Find the status function for me. It's on my global module. It's right there. It's real simple. All it does is add some text to a status box.
In here, I'm going to declare a constant called ONE_HOUR and that's going to be equal to 3600000. That's one hour. Remember, the timer event is dealing with milliseconds. That's one hour times 60 minutes times 60 seconds times 1,000 milliseconds, which is 3,600,000 milliseconds. So if you want half hour, 15 minutes, whatever, you can set it to whatever you want.
First thing we're going to do is just check and make sure that that form is open, because I know what happens. Sometimes you, the developer, get playing with it and you yourself close it. So we're just going to do a quick check.
If Not IsLoaded("AreYouThereF") Then we're going to load it.
So DoCmd.OpenForm "AreYouThereF." I'm going to leave it here. Load it hidden later if you want to. This is mostly just for you because your users can't close it. I've closed mine several times.
Now, every time Status is invoked, we're going to reset that form's timer. So every status that happens, it's going to just update the timer. If the timer keeps getting updated, then it'll never run to zero. The event will never run that opens up the "Are you sure?" message box, and it'll never steal focus. As long as you keep resetting the timer, the timer event never kicks in. That's the goal here.
We're going to reset the form's timer. With Forms!AreYouThereF (because we're going to do a couple things in With), we're going to say .TimerInterval = ONE_HOUR. Every time status is invoked, reset it to an hour. Reset it to an hour. It never gets to zero. Remember, it counts down.
We're going to also say .Caption = "Last Activity." Now so we can see what's going on. I want to put some other stuff here in a bit, but for now, this is good.
Save it. Debug compile. Oh, what we got? Oh, IsLoaded. We don't have our IsLoaded function. Let's go to the Code Vault. This is the Code Vault on my website. If you're a gold member, you can get all this cool code from here. If not, start typing. Copy it to my clipboard and we'll drop this in here. I'm going to put this in the TechHelp free template. Eventually, there's a couple of functions that I use all the time. Usually for these short guys, I end up getting rid of that space there so it's nice and short.
Now it should debug compile. There we go.
Now, every time Status is invoked, it's going to reset the timer on that form. So now we just have to make sure we have Status calls liberally throughout our database.
I'm going to close this for now. "Hello world," obviously does this, and then it loaded that form. See, and it's got the last activity, 7/26 at 05:00 PM. If I keep doing this, it's updating.
Part of the way you can help this database is just to throw a lot of Status calls around. In fact, sometimes what I'll do is make a Form_Open function that I call, and every time the form opens, it calls that status. In here, you could just do it right in the buttons if you want to: Status "Open," or Status "Open Customer Form" or whatever. Throw it in your log event, whatever, right? Status "Open Customer List Form."
Or you could put it in the form's OnCurrent event. You could do it in here if you move from customer to customer a lot. Design View, come in here, event, OnCurrent, make sure it's not going to interfere with anything else. You could say Status "Viewed" and let's do a Nz (actually, let's just do "Customer"). I was going to do a Nz like FirstName in case they didn't have a first name: Status "Viewed Customer ID" and then the CustomerID; they have to have one of those.
Now every time they move from customer to customer, it's just one more thing that's triggering a status. See, as I move through customers. Normally that'll be behind this window; you usually never see it, but it's updating the status down here. Watch, see, every time I move, it's updating.
You just want to keep this form getting updated in the background. There are lots of other ways. In fact, in the Extended Cut, I'm going to show you some other cool tricks too: key presses, mouse moves, all that stuff. But for now, we're keeping it simple, and I think for most databases, just the fact that opening a form and moving between records or opening a customer form, an order form, whatever, that's usually enough activity to keep this thing alive.
Remember, they're still going to get a pop-up that says, "Hey, are you still there?" and we're going to do that in tomorrow's video. So tune in tomorrow, same Bat time, same Bat channel.
Or if you're a member, you can watch it right now because I'm going to keep recording until I'm done tonight. That's one of the benefits of being a member; you can watch these as soon as they're posted. You don't have to wait for them to go public.
That's going to do it for your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part two.Quiz Q1. What is the main objective of the video tutorial?
A. To teach how to log users into an Access database automatically
B. To automatically log out users from an Access database after inactivity
C. To create user accounts in Access
D. To reset passwords in Access automatically
Q2. Why would you want to log out users from an Access database after periods of inactivity?
A. To save computer power
B. To prevent unauthorized access and perform backups or updates
C. To delete unnecessary data from tables
D. To increase database speed
Q3. What tool does Richard recommend for logging users out after inactivity?
A. VBA Message Box Timer Form
B. Importing an Excel macro
C. A scheduled Windows Task
D. SQL Server integration
Q4. What warning mechanism is presented to users before they get logged out?
A. Automatic shut down without warning
B. Email notification to the user
C. An "Are you there?" message box with a timer and response button
D. Phone call to the user
Q5. Which feature in Windows is mentioned as an additional security layer for protecting sensitive information?
A. Disk formatting
B. System restore
C. Windows screen saver locking the workstation
D. Installing antivirus software
Q6. To track user inactivity, the tutorial suggests monitoring which of the following?
A. Whether the user has saved their data
B. Whether the user is on the same form and control for long periods
C. The CPU usage on the user's computer
D. The number of errors thrown by Access
Q7. What is the purpose of the hidden "Are You There" form?
A. To display the active user's name
B. To log data changes as a backup
C. To run a countdown timer for inactivity and trigger the warning
D. To export reports to PDF
Q8. What is the recommended interval for the timer event in the "Are You There" form?
A. Every 5 seconds
B. Every minute
C. Every half-hour or hour
D. Every day
Q9. Why should the "Are You There" form be kept hidden from users?
A. To reduce database load time
B. To prevent users from closing it accidentally
C. To improve form design aesthetics
D. To limit the use of VBA
Q10. Where should the timer interval value (such as ONE_HOUR) be set according to Richard?
A. In the form's property sheet only
B. Hard-coded within the form's OnLoad event
C. As a constant in the VBA code
D. In Access options menu
Q11. What does the Status Box do as described in the video?
A. Sends notifications to the admin
B. Shows a visible countdown to users
C. Displays messages indicating user actions, such as opening a form
D. Prevents the database from closing
Q12. How is user activity tracked using the Status function?
A. By saving data in a hidden table
B. By resetting the timer every time the Status function is invoked
C. By analyzing the event logs in Windows
D. By emailing the admin on every action
Q13. What happens if the Status function is not called frequently in the database?
A. Access automatically optimizes queries
B. The timer runs out and the "Are you there?" check triggers
C. Data gets deleted automatically
D. Access database compacts itself
Q14. Where does Richard suggest placing calls to the Status function for optimal tracking?
A. Only on database startup
B. Only in the OnLoad event of the main menu
C. Whenever users perform actions like opening forms or changing records
D. Only during database shutdown
Q15. What happens when user activity is detected by the system described in the video?
A. The database shuts down immediately
B. The countdown timer for inactivity is reset
C. The database backs up automatically
D. The system logs out all users
Answers: 1-B; 2-B; 3-A; 4-C; 5-C; 6-B; 7-C; 8-C; 9-B; 10-C; 11-C; 12-B; 13-B; 14-C; 15-B
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 a common challenge: needing to log users out of a Microsoft Access database automatically when they become inactive. Many of us have had to track someone down just to close the database for a backup or update, or have encountered problems when someone leaves for the day without logging out, leaving the file open and complicating maintenance. Not only does this disrupt backup routines, but it can also leave sensitive information displayed on screen.
To address these issues, I will walk you through how to set up automatic logouts for inactive users in your Access database. The approach provides users with a warning prompt asking if they're still at their computer. If they respond, they stay logged in. If they do not, Access will close itself automatically after a set period.
This idea actually came from a question about how to log users out of an Access database automatically when there's no activity. The concern wasn't just about backups and updates, but also about preventing sensitive information from being visible to passersby if someone leaves their computer unattended. While a Windows screen saver can help lock the workstation after a period of inactivity, it does not actually close Access, which is why we're going to add this extra step within Access itself.
This session is geared toward developers. If you're new to VBA programming in Access, don't worry. I'll explain all the steps you need to get started.
You'll need a MessageBox Timer form, which allows you to display a message box with a timer and Yes/No buttons. When the inactivity period elapses, a message appears and gives the user a defined amount of time to respond. If they do not click the "Still Here" button, the database will close. You can find this resource in my earlier tutorials; make sure to familiarize yourself with it first.
You'll also need a Status Box, a feature in my free TechHelp template, which is useful for quietly tracking user activity. Rather than using a pop-up message that interrupts workflow, the Status Box can quietly display messages anytime a user opens a customer form or performs a related action. This helps us gauge whether someone is actively using the database.
Additionally, I'll use the IsFormLoaded function to check if any given form is open. We'll also keep track of what form and control the user is currently working with. If a user sits on the same field for a long time, it might mean they've walked away. For that, I recommend viewing my videos about working with active forms and controls to understand how these techniques help monitor activity. All of these resources are available for free on my website and YouTube channel.
For today's demonstration, I'm using my free TechHelp template, but you can implement these techniques into any Access database.
The first step is to create a hidden form with a countdown timer. You can set this timer to whatever period you like - a common choice is about an hour. The important thing is not to have it check activity too often, as forms with timers can occasionally interfere with focus, especially if you're working in the VBA editor. That's why I suggest setting a longer interval, such as half an hour or an hour.
Once you have your "Are You There" form set up, go into Design View and clean off any unnecessary fields. For clarity in development, I suggest adding a label that reminds you this is your inactivity checker, though you will hide this form from your users once finished.
The next piece is making sure this form opens with your main menu or whatever routine runs at startup in your database. You would set it to open hidden so users do not see or accidentally close it. Make sure you've followed the steps in my simple security video to prevent users from meddling with navigation or forms they shouldn't access.
While developing, keep the form visible so you can see how it behaves. Once you're satisfied, you can hide it and adjust things like record selectors and navigation settings based on your preferences.
Instead of relying on a visible countdown, which could interfere with your interface every second, keep the timer internal and at a longer interval. Personally, I prefer coding the timer interval rather than setting it manually on the form, as this helps keep everything centralized and easier to change.
Much of the logic relies on your Status function. Every time Status is called - such as when a user opens a customer form or takes another meaningful action - you'll reset the inactivity timer. By updating the timer on every valid activity, you ensure Access only checks for inactivity if nothing happens for the entire period. If there's continuous regular use, the inactivity check never kicks in.
You will want to scatter calls to Status liberally throughout your database. For example, you can add Status updates when forms open, when records are browsed, or even when buttons are clicked. For forms where users may move frequently between records, including a call to Status in the OnCurrent event is also a good idea. By doing this, you'll keep the inactivity timer refreshed any time the user does something significant.
In the Extended Cut of this video, I go into additional ways to detect user activity, such as monitoring keystrokes and mouse movements. However, for most databases, adding Status calls to the main forms and navigation is usually sufficient.
Remember, when the inactivity threshold is reached, the system triggers a pop-up message giving users a chance to confirm they're still at their desks. I'll cover the details of that part in tomorrow's video.
If you're a member, you can watch the continuation immediately, but if not, check back for the follow-up very soon.
You can find a complete video tutorial with step-by-step instructions covering everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Automatically logging out inactive Access users
Creating a hidden form with a countdown timer
Opening hidden forms on database startup
Resetting the timer based on user activity
Using a status function to track user actions
Implementing Status calls in various form events
Adding IsLoaded function to check form status
Formatting the hidden timer form for background use
Integrating timer reset into normal user workflowArticle Have you ever been in a situation where you need to log someone out of a Microsoft Access database - maybe to run a backup or install an update - but the user has walked away from their desk without closing the program? Not only can this cause issues with backups if the file is still open, but it can also leave sensitive information visible on the screen for anyone to see. Automating a logout in Access after user inactivity can solve these problems by both protecting your data and making administration much easier.
To automatically log out users after a set period of inactivity, you can add a hidden form to your database that will track user activity and trigger a warning before closing Access. If the user clicks "Still Here" within the set time, the timeout restarts. If not, Access will close itself.
Start by creating a new form that will act as your "Are You There" checker form. Copy any existing single form in your database and rename it to something like AreYouThereF. Open this form in Design View and remove any fields or controls; you do not need anything on it except perhaps a label for development purposes, as this form will run hidden in the background.
Next, ensure that your database opens this form automatically when it starts up. To do this, go to your main menu form or whatever form loads first in your application. In the OnLoad event for this startup form, add the following VBA code:
DoCmd.OpenForm "AreYouThereF", acNormal 'later you will use acHidden
During development, you may want to open the form normally so you can see it on the screen, but when rolling this out to users, hide the form so it does not get accidentally closed. Ideally, your database should be locked down enough that users do not have access to close hidden forms or dabble in the navigation pane.
Now, let's address how the form knows when to reset its timer. The simplest approach is to use a global Status function that gets called every time the user does something that should count as activity - like opening forms, switching records, or other common tasks. You should sprinkle calls to Status throughout your application wherever there is meaningful user activity. Here is an example of a Status procedure in a standard module:
Public Sub Status(msg As String)
Const ONE_HOUR As Long = 3600000 '1 hour in milliseconds
If Not IsLoaded("AreYouThereF") Then
DoCmd.OpenForm "AreYouThereF"
End If
With Forms!AreYouThereF
.TimerInterval = ONE_HOUR
.Caption = "Last Activity: " & Now()
End With
End Sub
This function makes sure that the AreYouThereF form is open, then resets its TimerInterval every time Status is called, specifying how long of inactivity to tolerate (3600000 milliseconds for 1 hour, but you can change this to 15 minutes, 30 minutes, or any other interval). Every time a Status call is made, the timer is reset, meaning the inactivity countdown starts over. As long as Status keeps getting called, the warning prompt and forced logout will never occur.
To support the code above, you will also need an IsLoaded function, which is a utility to check if a form is open:
Public Function IsLoaded(ByVal strFormName As String) As Boolean
IsLoaded = (SysCmd(acSysCmdGetObjectState, acForm, strFormName) And 2) <> 0
End Function
Once you have this logic in place, add Status calls throughout your app. For example, when you switch records in a form, you might put in the OnCurrent event:
Status "Viewed Customer ID " & Me.CustomerID
Or when clicking a button to open another form:
Status "Open Customer List Form"
You do not need to display the status to the user if you do not want to. The important part is that behind the scenes, your AreYouThereF form will keep getting its timer reset whenever there is user activity.
With the timer now tracking inactivity, the next step is to set up the Timer event on AreYouThereF so that when the period elapses with no activity, it triggers a warning prompt. This involves using a message box with a countdown timer, so the user has a chance to stay logged in. If they ignore the prompt, Access can be closed automatically in the Timer event. You would use your own message box logic here, perhaps something like:
Private Sub Form_Timer()
Dim response As VbMsgBoxResult
response = MsgBox("Are you still there? Click OK to remain logged in.", vbOKCancel, "Inactive User")
If response = vbCancel Then
Application.Quit
Else
Me.TimerInterval = 3600000 ' reset to 1 hour or your chosen interval
End If
End Sub
This is just a basic example. For a more polished solution, you might create a custom form with a countdown, allowing users a set amount of time to respond before auto-logout.
Keep in mind that you should call Status in as many logical places in your application as needed to properly track user interaction. This could include opening or closing forms, changing records, pressing certain buttons, or even custom functions tied to keypress or mouse move events if desired.
With this setup, your Access database will automatically prompt idle users before logging them out, helping ensure backups and updates go smoothly and that customer information is not left unattended. The key is to create the hidden timer form, open it on startup, reset its timer through your Status function wherever the user interacts, and trigger your warning and logout logic from the timer event. This keeps your application secure and manageable, no matter how many users you have or where they're located in your building. |
