Force Shutdown 2
By Richard Rost
2 years ago
Schedule Forced Shutdown, Kick Users Out, Part 2
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 2 we will setup a TempVar to track the database startup time. We will them setup a timer event in our main menu to check to see if the shutdown time has passed.
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, Access TempVar startup time, main menu timer event Access, check shutdown time Access, VBA timer interval, form timer event Access, force logoff database users, automate database closure Access, Access VBA DLookup, on timer event VBA Access, schedule maintenance Access database, database blackout times, user access control Access, set database to close, conditional shutdown Access, VBA date comparison
Intro In this video, we continue our Microsoft Access Force Shutdown series by building on the solution to log users out of your database when you need to perform maintenance or updates. I will show you how to use TempVars to record the database startup time, set up a timer event to check for scheduled shutdown times, and use DLookup and the NZ function to safely retrieve shutdown data from your server table. We will go over the timer interval setup, explain the shutdown logic, and see how to trigger and test a shutdown event. This is part 2.Transcript Today is part two of my Force Shutdown video series where I'm showing you how to kick all the users out of your database whenever you want. Whatever you decide, you want to be that guy, let's say beat it. They're going to go by you.
Of course, this is part two, so if you haven't watched part one yet, go watch it. There's the link. Come on back.
All right, so we split our database. We set up the server table. We've got it to the main menu. We'll close the database down. If it is closed, let's go into our front end again.
Now we're going to need to know when this database started up because if this database started up after the scheduled shutdown time, then that means that the user started it up after the last shutdown. So if the shutdown is 9 p.m., and this guy opened up at 9 a.m. the next day, then we don't need to shut down.
So what we're going to do is we're going to use a temp var. Temp var is a variable that will stay active in the memory. It even survives errors, but that's not important for this. Temp vars are nice and easy to use.
So in the form load event, the on load or on open, either one will work fine for this. We're going to say, we're going to say temp vars. What are we going to call it? We're going to call it startup time is now. Right now. That'll take the current date and time, put it in a temp var called startup time. So we can look at this and compare it with, hey, what was the last scheduled shutdown time? Are we after that? We're good. If one's coming up, then the current startup time will be less than when the shutdown time hits. Then we'll know that we have to reboot.
Now comes the tricky part, the timer event.
Now for practical purposes, you're going to probably want to set this timer event to be something like maybe once every 10 minutes or even once an hour. If you're concerned with just making sure everybody's out of the database at midnight because at 3 a.m. our backup routine runs, if that's the case, set it to once an hour or even once every two hours. Because if you set it to something like once a minute, then that form might steal focus. Like if you're over here working on the customer form, this thing over here is going to say, hey, I need a little bit of attention. If you're in the middle of typing or copying and pasting or something, it might interrupt what you're doing. It probably won't, but sometimes it does.
So I recommend make this timer interval as long as possible. 10 minutes should be fine, 15, 20 minutes, even once an hour I think is plenty. If you want the kind of control where you're doing maintenance throughout the day, and you want to say, all right, I need everybody out of the database in 5 minutes so I can post this update or refresh the system, make it once every 5 minutes. But if you're concerned, as a nightly backup event, make it once an hour. That's plenty.
For the purposes of class, though, I'm going to set mine to once every 10 seconds so we can see it doing something. I don't want to have to sit here and wait an hour for the thing to run for the thing to kick in. So I'm going to find my form timer interval right there. Now this is in milliseconds. So if I set it to 1000, it's going to run once every second. If I set it to 10,000, it's going to run once every 10 seconds. If you want every 60 seconds, you do that. If you want every 60 minutes, you multiply that by whatever.
We got seconds times 60. That's every 60 seconds times 60. That's once an hour. Every 60 minutes. Now this can hold, I think it's a long so it can hold lots. It can hold billions. But that's good. So I'm going to set mine to once every 10 seconds - 10,000.
Once again, the logic is going to be, we're going to look in this server table when this event runs. We're going to say is this less than now. So in other words, if we've passed the shutdown time, that means we need to check to see that we know, hey, we want to have to shut down. Let's take a look at what's the startup time of the database. If the startup time of the database is less than that, we need to shut down, because the next time it starts up, it'll be greater than that. If the shutdown time is in the future, then we don't have to worry about it at all.
So that's our logic. And by the way, I was thinking after I set this up in the last video, you could easily expand this if you want to have like a blackout period. If you want to say, ok, I want everybody out of the database from midnight to 6 a.m. You could have a shutdown start time and end time in here. Instead of just looking at one day, you can say, does the current date time fall between the blackout times? Then if they try to start the database, it kicks them right back out again. That's very easy to do. If you guys can't figure it out, or you want to see me do it, let me know. If enough people comment on it, I'll put it in a future video. But I think it's pretty easy to figure out once we get to this point.
Shutdown start time, shutdown end time. When the system boots, look at those two dates and if the current date falls between them, just exit the database.
Now in our timer event, let's go back over here, find the on timer event, come in there. This is where the meat and potatoes go. First up, we need to look and see what the current shutdown date time is. So we need to declare a variable, Dim ShutdownDateTime As Date, and we're going to look that puppy up.
So ShutdownDateTime equals DLookup. I should have put DLookup in the prerequisites, but I think most people who get to the VBA level know what DLookup is. If you don't, here's my video on it. Go watch this; it explains how to use the DLookup. Also, while you're at it, watch my NZ function too. This is in case you look up a value and it's null. It shouldn't happen because we control what's in here. The users can't get to ServerT, so you should make sure there's a value in here. But just in case you forget to put a value in here, we'll handle it with an NZ in just a second.
So what are we looking up? We're looking up ShutdownDateTime. That's the field from ServerT. Normally you put criteria here, but we don't need criteria because there's only one and only one record, so it's going to just get the first record.
This is where the NZ comes in just in case that value is null. You don't want an error. So NZ and give it a value. We'll use the same value we did before: 2100-1-1. That's January 1st of 2100. Of course, VBA still flips it. Oh, it usually flips it. Oh, it didn't flip it because I didn't put it in these things. That's my bad. Put them inside a date, the date hashtags, and now it flips it. There it goes.
Note to the dev team: Leave it the ISO standard. Don't put it like this. I have my Windows date time format set to ISO 2100-01-01 and VBA still flips it. So whoever is keeping my list for me for the Access dev team, put that on it.
All right, so now I know when the next scheduled shutdown date time is. It could be in the past, it could be in the future. Let's put a little something in here too. Let's put down Status: Checking for shutdown. This is just for us. You could leave this in if you wanted to. You want the end user to see this. And I'm going to put a little format now as like an HH:MM:SS kind of thing. It's going to put the current time in there, formatted like that, just so we can watch it in the status window.
In fact, let's just test what we got right now. Save this. Debug, compile. Close this. Take a peek. Every 10 seconds it should pop that little status message. It's telling us that code's running. Not doing anything yet, just doing a little DLookup. There you go. And yes, it's currently 5:04 a.m. Perfect. Every 10 seconds that'll pop up in there. It just tells that our code is working.
Now, I'm going to shut this because while you've got a form with a timer event running on it, if you go to the VB window, it's still running, and it's going to mess up your VB editing sometimes. So you're going to close that. One of the reasons why I wanted to put that exception in there: for me, the developer, I'm constantly going in and out of that form while I'm working on it. So to have the database shut down every time is just annoying for me.
Back here. So now we've got the shutdown date time. What we're going to say is if this shutdown date time is in the past, that means we have to check because we might need to shut down the database. So if shutdown date time is less than now (or equal to; we can make it or equal to now), we're going to shut down right now. Then, we may need to shut down. Check when the database started. Otherwise, we don't need to shut down; it's scheduled in the future, so just do nothing.
Yes, sometimes I will do this. I'll put If and Else blocks to explain the logic to myself later on. I used to be all against writing comments because I've always been a lone gunman. I never work in development teams. I never have, and so I don't bother leaving comments a lot of the time until 50-year-old me is looking back at code that 40-year-old me wrote and I'm like, what was I thinking? So now 50-year-old me leaves comments for 60-year-old me so when I come back to it 10 years from now, I can see, hey, what was that all about?
So at this point, now we have a shutdown date time that's in the past. So now I need to say, hey, did the database start up after that shutdown date time? If so, we're good because it was shut down at the shutdown time and the user just started it up afterwards. Again, you'll change this logic if you want to make it so that you've got blackout dates or blackout times. But we're just making sure that the database itself was restarted at that moment.
So if temp vars start up time is greater than the shutdown date time, then the system started after the last forced shutdown, so we're OK. Here you can just Exit Sub if you want. Else, the system started before the shutdown was issued, so we need to shut down. Status: Must shut down or whatever.
Now, how are you going to actually affect this? What do we actually do? Well, I'm going to open up a form, a shutdown form, that will display a message for the user. Yes, it's going to steal focus, so if they're in the middle of typing, this form is going to pop up in their face. But they can easily set it aside for a minute or two, finish what they're doing, and then close up the database. This thing is going to pop up in their face and they'll have a minute or 10 minutes or an hour or however much time you want to give them. We'll discuss that in a minute.
At this point right here, DoCmd.OpenForm "ShutdownF"
And you could even make, if you want the database to talk to them, speak something. I have a whole lesson on making your database speak. You can have it say "Yo, database going shut down. Beat it." Go watch this video and see how that works.
So then we're going to End If here, and that's pretty much it.
Now all we have to do is build this shutdown form. The shutdown form is going to display a message for them, say "Hey, the database is going to shut down in a minute" or 10 minutes or whatever. Then that form will have a timer in it that will actually do the shutdown. That's how that's going to work.
We will build that shutdown form in part three tomorrow. Tune in tomorrow, same bat time, same bat channel. Members, you can watch it right now because I'm going to be recording it in about five minutes. For everybody else, that's going to be your TechHelp video for today.
Hope you learned something. Live long and prosper. I'll see you tomorrow for part three.Quiz Q1. What is the main purpose of the timer event in the force shutdown system described in the video?
A. To automatically backup the database every hour
B. To regularly check if the database should force users to log out
C. To display the current time to users
D. To optimize database queries periodically
Q2. Why does the instructor recommend using a longer timer interval (like once an hour) in most cases?
A. Frequent intervals make the code run slower
B. Short intervals can interrupt the users workflow
C. Longer intervals make the database more secure
D. Timer events cannot run more than once an hour
Q3. What is the purpose of storing the startup time in a TempVar called "startup time"?
A. To remember when the last backup was performed
B. To determine if the database was started before or after a scheduled shutdown
C. To track user login attempts
D. To identify which user is currently connected
Q4. How is the shutdown datetime retrieved in the VBA code?
A. By reading from a local text file
B. By querying the front end user settings
C. By performing a DLookup on the ServerT table
D. By using an SQL query to external server logs
Q5. What is the function of the NZ function in the context of DLookup for shutdown datetime?
A. To skip null values in query results
B. To allow DLookup to retrieve multiple records
C. To supply a default value if the shutdown datetime is null
D. To count all occurrences of a value in a table
Q6. In the discussed logic, what happens if the database's startup time is greater than the shutdown datetime?
A. The database is immediately forced to close
B. The shutdown form is shown to the user
C. No action is needed, the user started after last shutdown
D. The code resets the timer interval
Q7. What is one possible enhancement mentioned for the force shutdown system to cover blackout periods?
A. Automatically email all users before shutdown
B. Use shutdown start time and end time fields to define a blackout range
C. Make the shutdown form display in multiple languages
D. Enable shutdown only on weekends
Q8. Why does the instructor set the timer interval to 10,000 milliseconds during the tutorial demonstration?
A. To minimize system resources used by the form
B. To quickly see the timer event executing for demonstration
C. Because timer intervals below 10,000 are not allowed
D. To ensure the timer event never interrupts other processes
Q9. When deciding whether to shut down, which two items are compared using logic in the timer event?
A. Current user ID and backup time
B. Startup time (from TempVar) and shutdown datetime (from ServerT)
C. Timer interval and system clock speed
D. Form status and timer interval
Q10. What is the intended user experience when the shutdown form is opened?
A. The database immediately closes without warning
B. The user is given a message and some time to finish up before shutdown
C. The users are logged out and data is deleted
D. The database sends an email notification and then restarts
Answers: 1-B; 2-B; 3-B; 4-C; 5-C; 6-C; 7-B; 8-B; 9-B; 10-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 continues with part two of my Force Shutdown series, where I am demonstrating how you can force all users out of your Microsoft Access database whenever you need to. If you have not yet gone through part one, I recommend you review that first, then come back here so the steps make sense.
At this point, you should have already split your database and set up the server table as described previously. After closing the database, reopen your front end, because now we need to keep track of when the database is started up. This is important because if a user launches the database after the forced shutdown time has already occurred, there is no need to force them out again. For example, if the required shutdown happened at 9 p.m. and the user opens the database at 9 a.m. the next day, there is nothing further to do.
To handle this, I use a TempVar, which is a type of variable that stays active in Access while the database is open, and even persists if errors occur. TempVars are simple and effective for this purpose. When the main form loads (using either the On Load or On Open event), I recommend storing the startup time in a TempVar. That way, you have a reference for when the database was launched, and you can compare it to the scheduled shutdown time. If the current startup time is prior to the shutdown time, you know the database needs to be closed and restarted. If it is after, the user is fine.
Next, we need to address the tricky part — the timer event on the main form. In practical terms, you might want this timer to check only every ten minutes, or even once an hour, especially if you just need everyone out of the database by midnight for backups. If the timer runs too frequently (for example, every minute), it can steal focus from your active forms and interrupt users working in other areas, such as typing or copying data. Even for maintenance during the day, a five-minute interval is usually sufficient to ensure people exit in time. But for a typical nightly event, once each hour is plenty.
For the purposes of this lesson, I will shorten the timer interval so we can observe the results more quickly. Remember, Access timer intervals are measured in milliseconds, so a value of 1000 equates to one second. For ten seconds, use 10,000, and multiply accordingly if you want minutes or hours.
With the timer event set up, each time it fires, we want to check the server table to see the current scheduled shutdown time. The logic is pretty straightforward: If the shutdown time has already passed, check whether the database was started after or before that time. If it was started before, then the user should be forced out; if after, they are okay.
While writing this code, I realized it is easy to extend this system if you ever want more control, such as enforcing blackout periods (for instance, kicking users out from midnight to 6 a.m.). In that case, you would just add both a shutdown start and shutdown end time, and the logic can check if the current time falls between those values. Then, when a user tries to start the database during this period, they are immediately kicked out. If anyone is interested in seeing how to set that up, let me know and I can create a separate lesson for it.
Back to our current process. When the timer event runs, we look up the scheduled shutdown date and time using DLookup on the server table. I recommend wrapping this in the Nz function for safety, in case there is ever a null value, so your code does not error out in unexpected situations. Set a default far in the future, such as January 1, 2100, so nothing happens by accident if there is a misconfiguration.
At this point, it can be helpful for development purposes to display some kind of status indicator, such as updating a label with "Checking for shutdown" and the current time in HH:MM:SS format. This helps show that the code is executing.
Remember, while the form is open and its timer event is active, if you jump into the VBA editor, the timer is still firing in the background. That may interfere with editing, so be sure to close the form if you are working on code.
Now, the main logic works as follows: If the scheduled shutdown time is in the past (or at the current moment), check if the database was launched after that. If yes, the user already restarted after shutdown, so nothing further needs to be done. If the database started before the shutdown, or at the shutdown time, you need to trigger the shutdown process.
When it is time to force shutdown, I display a shutdown form that notifies the user the database will close shortly. Yes, this form will take focus, but the user can dismiss it or finish what they are doing if you allow a slight grace period. You can also have some fun and make your database speak a message to the user if you like; I have a separate lesson on that as well.
What remains is to build the actual shutdown form, which will display the warning and then, after a set period, close the database. I will cover the details of creating this shutdown form in part three of the series.
That wraps up today's TechHelp tutorial. You can find a complete video tutorial including detailed, step-by-step instructions covering everything I discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Using TempVars to store database startup time
Adding code to Form Load to set startup time
Implementing logic to compare startup time to shutdown time
Setting up a timer event to check for shutdown
Configuring the timer interval in milliseconds
Calculating appropriate timer intervals
Using DLookup to retrieve scheduled shutdown time
Wrapping DLookup with NZ to handle null values
Formatting a status message with current time
Writing logic in the timer event to trigger shutdown
Comparing shutdown time and current time
Detecting if the database was started after shutdown
Conditionally executing shutdown routines
Opening a shutdown form to notify users
Planning for a shutdown notification form |
