Force Shutdown
By Richard Rost
23 months ago
Schedule Forced Shutdown. Kick Users Out! Byeeee...
In this Microsoft Access tutorial, I'm going to teach you how to schedule a forced shutdown time for all users logged into your Microsoft Access database. In Part 1 we will create a server information table so the admin can set the shutdown date/time. We will split our database. We will make it so that if users close the main menu, the database shuts down because this is where our timer event will be running and we don't want that form closed with the database still open.
Colin from Pasadena, Texas (a Platinum Member) asks: We have a backup routine that runs every night at 3:00 a.m. The problem, as I'm sure you're aware, is that some users go home for the night and forget to log out of their database, and our backup routine will not back up any open Microsoft Access files. It also makes it hard for me to do maintenance in the morning if I come in early and there are still people logged into their machines. Then I have to run around and shut down databases. We only have 15 users, but it's still a pain in the behind to have to log out three or four people every day. And of course, they don't listen to me. Is there a way that I can have Microsoft Access force users to shut down the database at a specific time, say 11:00 p.m.? No one's ever here outside of regular business hours, except for me, of course - the lonely IT guy.
Members
There is no extended cut, but here is the database 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!
Prerequisites
Links
Up Next
Keywords
TechHelp Access, forced shutdown, Access database maintenance, backup routine, close Access on timer, VBA programming Access, server-side shutdown scheduling, ACCDE encrypted database, multi-user database management, automate Access shutdown, nightly backup Access, kick out users Access, TempVars, environment username Access, network file sharing Access, Kick users out, Kick Users Off, Remote shutdown, Boot users, Force restart, Force reboot, Force shutdown
Subscribe to Force Shutdown
Get notifications when this page is updated
Transcript
In today's video, I'm going to show you how to schedule a forced shutdown time for all users in your Microsoft Access database. That's right. You want to kick everybody out of the database at 11 p.m. or 3 a.m. or whatever. We're going to do that today.
Today's question comes from Colin in Pasadena, Texas, one of my platinum members. Colin says, we have a backup routine that runs every night at 3 a.m. The problem, as I'm sure you're aware, is that some users go home for the night and forget to log out of their database. Our backup routine will not back up any open Microsoft Access files. Yes, most backup programs will not back up open files.
There are some that do and there are some alternatives, but if you have a backup solution that you like, I can see where this is a problem. It also makes it hard for me to do maintenance in the morning if I come in early and there are still people logged into their machines. Then I have to run around and shut down databases. We only have 15 users, but it's still a pain in the behind (and he didn't use the word behind in his email) to have to log out three or four people every day. Of course, they don't listen to me.
Is there a way I can have Microsoft Access force users to shut down the database at a specific time, say 11 p.m.? No one's ever here outside of regular business hours except for me, of course, the lonely IT guy. And yes, Colin, I feel your pain. I was the lonely IT guy, the lonely database administrator for a couple of companies in my career. So I get where you're coming from.
But yes, of course, it is definitely possible to program Access to force everybody out at whatever time you specify. That's what we're going to cover in today's video.
But first, some prerequisites. This is a developer level video. What does that mean? That means if you've never done any VBA programming before and you want to learn how, go watch this video first. It'll teach you everything you need to know to get started in about 20 minutes.
Make sure you know how to write If Then statements. If not, go watch this video. Make sure you understand what a split database is. If you've got multiple users on your Access database, you must split your database. You don't have to. You can have them run the same copy of the database, but I don't recommend it. That's just asking for problems. So if you've got multiple users in your database, your database should be split. Go watch this video if you don't know how to do that.
We're going to use a TempVar. If you don't know what TempVars are, go watch this. It's basically a temporary variable in memory. Adam loves them, and so do I. We're going to use them in today's video.
This one's optional, but if you want to know who's in your database at any one given time (who's logged on, who's logged off), go watch this. We're not going to use all of this stuff today, but I am going to use the environment variable so that you can have it so your system, the administrator's system, doesn't have the reboot forced on it in order to know who's logged on. We're going to use one of these techniques in this video. This one's optional, but go watch this. It's really good, especially if you've got a multi-user database.
Now, these are all free videos. They're on my website. They're on my YouTube channel. Go watch them and come on back.
Alright, so for this example, we're going to use my TechHelp free template. We're going to pretend that we have four or five different users on a network, on a local area network, using it. We'll simulate a file server, a database server, by just creating a folder on our desktop. We'll call it DB. This would be whatever shared folder you have set up on your server, which doesn't have to be an actual server.
A lot of people ask me that. It doesn't have to be like a Windows server. No. You could have a peer network in your office, and you have three or four machines running Windows, and you have file sharing between them. You just set up a shared folder on one of them. That's all you need for a database server as it is with Microsoft Access. It's not a real database server, but it's good enough. It's good enough for small offices. But the technique that I'm going to show you will even work if you have a SQL Server running Microsoft Access. You just have to set up a shared table that is going to include a shutdown time. You just have to set that up on your SQL Server instead. But the front end stuff is all exactly the same.
Before we split this thing, let's go in here and set up a table that gets the commands from the admin. We're going to call it the server table or the shutdown datetime. Open this guy up here. We're going to create a table in here, create table design. We're going to have a shutdown datetime field. Save this as the serverT, the server table.
Primary key: now, we really don't need one. This is one of those rare instances where it's going to be a table that has one record in it, and so you don't need an ID because you're not going to have multiple records. The admin will open up this table. If you want to make yourself a form for it, go ahead. You don't have to. You're just going to open up this table and come in here and put down what date and time you want this thing to shut everybody down.
For now, let's put in here a date way in the future, like 2100-01-01. Yes, I use ISO date standard time. If you don't know what that is, I have a video about it. I'll put it down below in the links section. You can go watch that. I put it down here as month-day (which everyone should use).
Later on, let's say today is, I'm recording this on January 29th, 2024 at about 5 p.m. If I want this to shut down tonight, I would just put that date and time in here. Maybe 10 p.m. We'll do an example of it a little later on.
So there's the server table. This thing is going to be linked to all the front ends. I'll be able to look in here and say, "Hey, do I have to shut down now? Okay. How about now? No? Okay. So how about now?" We'll make an interval. We'll make a timer loop that will keep checking that. The interval is up to you. We'll talk about that in a few minutes.
Now it's time to split this thing. I have a whole video on splitting databases, which is what I told you to watch before. First, let's move this guy into the database folder. Open that up. You don't have to put it in a database. This just simulates your network file share. I'm going to rename this to my front end, and I'm going to copy it and paste it. Paste. This will be the back end.
Open up the back end. Shut this, and delete everything except for the tables. All these objects, delete. Are you sure? Yep, all go away. Just the tables in my back end. While you're at it, go into File - Options and make sure under Current Database, you don't have a display form. That usually goes away when you delete the display form, but sometimes it doesn't and you get an error message.
Back end's all set. Let's go into the front end. In the front end, we're going to delete just the tables. Are you sure? Yes. Deletes the relationships? Yes. That's OK. Now we're going to link it to that back end file.
Database tools - no, sorry, External Data. New Data Source - From Database - Access. To Data Source, browse. This is on my desktop under DB. Back end, open, OK. Then we're going to select all and hit OK. Now we're linked. Basic split database setup.
Step three. We need to have a timer event running somewhere. You could open up, like when this thing starts up, you could open up and hide a hidden form. That's definitely one option. I've seen some different solutions that involve that. But me, in all of my databases, I like to always have the main menu open. I usually turn this box off here so the user cannot shut the main menu. Since my end users are working with ACCDE files, they can't right click and go to design view. That's turned off. That's a properly encrypted database, which I talk about more on my split database video.
Just in case you do give them the option to close this, I'm going to say that if the main menu is somehow closed, we are going to shut down the database. In this form's On Close event (or On Unload, it doesn't matter, either one works fine), put it on close. Here, if this form is closed, we're going to do Command.Quit. The default is acQuitSaveAll. That's fine. Just hit that, enter.
It's going to basically prompt the save all, but your end users can't do that because they can't save stuff, so don't worry about it. One thing that I find annoying myself is if I'm working on the database, I'm often switching this thing between regular and design view and so on. I'm going to use the techniques that I talked about in my logging users on and off video to say, "Hey, if this is me, if it's developer guy, if it's user Rick on my machine, I don't want this to shut down the database."
I'm going to use those environment variables that I talked about in the other lesson. If Environ("UserName") (which means the user logged on to Windows) is not me (my username is AMICR, don't ask, long story; I think I talk about it in that other video), and Environ("UserDomain") (that's the name of the PC, the Windows name of the PC), if that's not SPOCK, that's fine, then we're going to quit.
That basically just says, "If it's me on my PC, then don't exit the database," because I like to flip back and forth between doing my maintenance and my design view and all that. If they close the main menu, it's going to exit them out because our timer is running in this form. If they shut this and leave the database open, that defeats the purpose. See how that works? So that's what that's about.
Now I can close this, save changes, and start it back up. There we go. If you want to test this, by the way, just do one of these, run that stuff out. Always good to throw in a quick Debug - Compile.
Now if I close this, if you want to save changes, see, because it knows it's closing now, so it asks me if I want to save changes and then it exits the database. I don't want that to happen for me. Let's go back and fix that. See, here's what happens, though. If I switch to design view, see, Command.Quit is unavailable right now, because it causes issues. Now I need to go back into my code. OK, I'm OK.
Now, we've got our server table set up. We've split the database. We made it so they can't close down the main menu form because our timer event is going to run here.
What's up next? Next, we're going to make the timer form, which is going to involve some logic. We're going to have to look up what the server setting is going to be for any given moment. We're going to have to set the time that this thing started up. We're going to build a form to let the user know, "Hey, you're going to be shutting down soon." It's going to look like that: "Database will shut down in one minute" (or 10 minutes, whatever you want).
Then we're going to test it and issue some commands to it. We're going to continue tomorrow in part two. So tune into my real same bat time, same bat channel. Of course, members, silver members and up, can watch part two right now because I'm about to record it and I'm posting the time.
You're watching this one Tuesday, the 30th. Wednesday, the 31st, the rest of you can watch it. If not, sign up, become a member, and you can watch it right now.
That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part two.
Quiz
Q1. What is the main goal of the technique described in the video?
A. To automatically back up open Microsoft Access databases
B. To force all users to log out of the Access database at a specific time
C. To add user-level security to Microsoft Access databases
D. To synchronize Access databases with SQL Server
Q2. Why is it a problem if users leave the Access database open overnight, according to the video?
A. Access won't perform calculations correctly
B. Backup routines typically cannot back up open Access files
C. Multiple users can overwrite each other's work
D. It increases the risk of database corruption
Q3. What prerequisite knowledge is suggested before following the technique in the video?
A. How to build Access web apps
B. Experience with SQL Server Management Studio
C. Basic VBA programming including If Then statements
D. How to use Access macros only
Q4. What is a split database, as mentioned in the video?
A. An Access database optimized for mobile users
B. A database where tables and code are combined in a single file
C. An Access setup with separate front-end and back-end files
D. An Access database that supports simultaneous SQL and VBA code execution
Q5. What is the primary role of the "serverT" or "server table" in this technique?
A. To store transaction logs for auditing
B. To hold the shutdown date and time for controlling forced logouts
C. To assign permissions to different users
D. To manage database backups
Q6. According to the video, what is recommended for dealing with the shutdown time record in the server table?
A. Have multiple shutdown time records for each user
B. Do not use a primary key since there will only be one record
C. Use a unique primary key for each shutdown attempt
D. Share shutdown time records across different databases
Q7. What is the ISO date standard format favored in the video for dates?
A. MM/DD/YYYY
B. DD/MM/YYYY
C. YYYY-MM-DD
D. Month-Day-Year
Q8. After splitting the database, what is the next step in the setup described in the video?
A. Create a timer form to regularly check the shutdown time
B. Build custom queries for user activity logging
C. Import the server table into the front-end
D. Set up replication between front end and back end
Q9. What does the timer event in the main form accomplish?
A. Updates user records every second
B. Regularly checks if the server table shutdown time has passed
C. Automatically syncs data with SQL Server
D. Audits every database change
Q10. Why does the video recommend using environment variables like Environ("UserName") and Environ("UserDomain")?
A. To prevent certain users or computers (such as the developer or admin) from being logged out
B. To store temporary data in user sessions
C. To enforce network-level encryption
D. To log every user action in a log table
Q11. What happens if the main menu form is closed by a user other than the developer?
A. Nothing, the database stays open
B. The user is prompted to re-enter their credentials
C. The database will be shut down by running Command.Quit
D. The user is switched to a backup database
Q12. If your Access application is connected to a SQL Server backend, what needs to change in the described setup?
A. The entire setup will not work
B. Set up the server table on SQL Server instead of Access
C. Only Access macros should be used
D. No changes are needed to the setup
Q13. What is the purpose of using a form to display a warning like "Database will shut down in one minute"?
A. To test if the front-end is connected to the back-end
B. To warn users so they can save their work before being forced out
C. To reset the user's session time
D. To prompt users for a password
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-B; 7-C; 8-A; 9-B; 10-A; 11-C; 12-B; 13-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 TechHelp tutorial from Access Learning Zone is about how to schedule a forced shutdown time for all users in your Microsoft Access database. This is a great way to ensure that everyone is logged out by a certain time, like 11 p.m. or 3 a.m., which is particularly useful if you have nightly backup routines or need to perform regular maintenance on your database.
The question originates from a user who manages a relatively small network of about 15 users. He's run into the common problem where users forget to log out at night, which prevents the backup processes from copying open Access files. This is a limitation of most backup software, as they typically will not back up files that are still open. Additionally, he's had to spend time after hours tracking down who is still logged in so he can close their sessions, which is time-consuming and frustrating.
If you are in a similar situation and want a way for Microsoft Access to automatically log everyone out at a specific time, it is absolutely possible to set that up. I have been through this myself during my career as a database administrator, and I know how helpful this kind of solution can be.
Before you proceed, there are some important prerequisites. This is a developer-level lesson, so if you have never worked with VBA programming in Access before, I recommend you spend some time learning the basics first. You should understand how to write If Then statements, as well as what a split database is and why it's necessary for use with multiple users. If you are not familiar with these concepts, I have free tutorials available that will get you up to speed.
Additionally, we'll be making use of TempVars, which are temporary variables that live in memory during your session. These are quite handy for this sort of operation. I also recommend learning about how to track user logins and logouts using environment variables, so the forced shutdown can exempt the administrator or others as needed. Again, there are free videos on all of these topics on my website and YouTube channel.
For the main example, I'll be using the TechHelp free template, and I'll simulate having four or five users all accessing a shared folder on a local area network. Your "server" in this case doesn't have to be anything fancy. For many small offices, a shared folder on a Windows machine is all you need for Access, though the same technique will also work if you're using SQL Server with Access as the front end. The key is to have a shared table that every user's front end can reference for shutdown instructions.
First, we'll set up a special table in the database to store the admin's shutdown commands. It usually contains a single field, such as "shutdown datetime," and doesn't need a primary key since it will only ever have one record. As the admin, you or someone responsible can update this table to indicate when all users should be forced to log out. For initial setup, you might enter a date far out in the future so the database does not immediately issue a shutdown. Whenever maintenance or backup is needed, just enter the target date and time into this table.
Once the shutdown time is stored in the table, each front end can check this value to determine when it is time to log off. We'll set up a timer loop in the Access front end that regularly checks this table. You can determine your preferred check interval; we'll discuss that in detail during the implementation.
Next, it is time to split the database into front end and back end files. The back end will contain only the tables, including our new shutdown instruction table, and lives on the shared folder. The front end, which all users receive a copy of, will link to these shared tables. After moving the files into the appropriate locations, be sure the front end is properly linked to the tables in the back end.
For the shutdown mechanism, we need a timer event that is always running. A typical way to achieve this is by having the main menu form open and running in the background at all times. I usually set the main menu so it cannot be closed by users. Only developers have access to special options, and when working with ACCDE files in a properly managed database, users cannot break this restriction.
There is an extra safeguard here: if the user does manage to close the main menu form, then the database will shut down automatically using standard Access commands. However, when I am working on the system as an administrator or developer, I don't want to be kicked out during debugging or maintenance. To handle this, I use environment variables to identify my login and computer name, so I can exclude myself from the forced shutdown.
Once these elements are in place, you will have a main menu form with a timer that checks the shutdown settings stored in the back end. If the scheduled shutdown time is reached, the form will display a warning, such as "Database will shut down in one minute," giving users time to save their work before Access automatically quits.
In summary, this approach involves the following key steps:
1. Set up a shared table in your back end database to store the shutdown time.
2. Split your database so each user has a front end linked to the shared back end.
3. Add a timer event in the main menu form of the front end that checks the shutdown table.
4. Exempt designated users, such as the administrator, from forced shutdown using environment variables.
The detailed creation of the warning and shutdown features, including timer setup and logic, will be tackled in the next installment. If you are a member, you can access part two right away. For everyone else, check back tomorrow for the follow-up.
You can find a complete video tutorial with step-by-step instructions covering everything we discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Creating a shared folder for an Access database on a network
Simulating a database server using a local folder
Creating a table for scheduled shutdown commands
Designing a table to store a shutdown datetime value
Linking a shared server table to the Access front end
Splitting a Microsoft Access database into front end and back end
Moving tables to the Access back end
Linking front end to back end tables in Access
Setting up the front end to remove tables and establish links
Configuring the main menu form to always remain open
Adding shutdown logic to the main menu form's On Close event
Using Command.Quit to force database application shutdown
Using Windows environment variables to identify the current user
Exempting specific users from forced shutdown based on user and PC
Implementing a timer event to check for scheduled shutdowns
Discussing the role of a timer loop to poll for shutdown time
Ensuring the timer form notifies users before shutdown
|
