Blackout Times
By Richard Rost
2 years ago
Setting a Blackout Time to Block Database User Logins
In this Microsoft Access tutorial, I will show you how to set a blackout time to prevent user logins during specified hours, ensuring your database remains available for maintenance or backup.
Sami from Windsor Locks, CT (a Moderator) asks: [Followup to Force Shutdown video]: I would love for you to show how to do a blackout period where we only store time without a date portion. This way every day between the two times specified, the system will kick users out and also prevent them from logging on.
Members
In the extended cut, we will specify the shutdown message and timer interval for the shutdown form. You'll learn how to customize the message to indicate either a normal shutdown due to maintenance with a longer timer or an immediate shutdown during blackout periods. Additionally, I will show you how to set which days of the week the blackout times apply, suitable for varying maintenance schedules.
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
Recommended Courses
Keywords
TechHelp Access, blackout time setup, prevent user logons, Access database security, automated logoff, user access control, prevent database login, database maintenance automation, user logoff automation, configure logoff times, maintenance window, multi-user database, user login restrictions, manage database access, schedule user logoff, daily maintenance period
Subscribe to Blackout Times
Get notifications when this page is updated
Intro In this video, I will show you how to set up blackout times in Microsoft Access so that users are automatically logged out and prevented from logging in during scheduled maintenance periods. We will talk about adding blackout start and end times to your server table, updating your front end and back end structure, and writing the code necessary to check the current time and enforce these blackout periods. I will demonstrate how to set up timer events to apply these rules and make real-time adjustments as an admin, as well as test the blackout functionality and show options for user notifications and shutdown messages.Transcript Today, I'm going to show you how to set a blackout time for your database. If you have a multi-user database set up where they're logging on at different times throughout the day, but you want to have it so that okay, every day at 9 p.m., I want to make sure everybody's out of the database because from 9 to 9:30 we do maintenance. So if they're logged in, it'll kick them out. And if they're not logged in but try to log in, it'll say, nope, the database is in blackout time, go away, come back later. Okay?
Today's question comes from my friend Sammy from Windsor Locks, Connecticut. He's one of the moderators on my website. This is a follow-up to a previous video I did called "Force Shutdown," where you can set a time and date that you say, okay, I'm planning maintenance tomorrow at 3 o'clock, so anyone that's in the database will be kicked out at that time. Sammy says, I would love for you to show how to do a blackout period where we only store time without a date portion. This way, every day, between the two times specified, the system will kick users out and also prevent them from logging on. Very good. This is also handy if you have a nightly backup. Your backup runs at 10 o'clock, you want to make sure that everybody's out of the database at 9:45 right? If you have not yet watched my force shutdown videos, go watch these first. This is a three-part series; you'll find it on my website and on my YouTube channel. You'll find links to it down below. If you are a gold member, go to the force shutdown three-part three page and grab the database right here if you want to work off of my copy, and if you're not a gold member, well, you just have to build a database along with me following with the videos. So there you go.
Alright, so here's the database that we've built in the force shutdown series. Now in that database, I split it so that we have a front end and back end. I'm going to get rid of front end two and three. These are just copies. We use these to simulate having multiple users in the database. And just as a refresher, let me open up the front end here. And this guy's got a timer event where it checks for a shutdown. It looks in the server table. Remember this one got a shutdown date and time. Now this was set way back in January when I originally recorded it. It's currently May 3rd of 2024. So it's gonna watch for this to happen and if this date happens after the time that the user logged in, it's gonna log them out. The problem is what they just turn right around and try to log back in. Alright, that's what Sammy's trying to get at. He wants everybody out of the database between two times. OK?
So the first thing we're going to do is add two fields to our server table to indicate the blackout start time and the blackout end time. So let's go back to our back end file. Let's edit this guy, back end. And we'll go into our server table, design view. We're going to add in here blackout start time but no. This will be a date-time value. Remember a date-time value can store a date or a time or both together. Okay, then we'll have a blackout end time. Okay, save that. Let's put some start values in here. All right, it's currently 6:39 PM as I'm recording this. So I'm gonna put in here, let's put in here 6 PM to 8 PM. I know, it's a big, wide, you'll have in there whatever your backup window is: 2 AM to 3 AM or whatever. But for class today, to make sure that this is working, we're gonna go with those two times, okay. Let's close this, save changes, sure. We'll go back over to our front end. And always remember when you're working between a front end and back end, if you're going to make changes to the tables, shut down the front end first because if you don't, this guy caches what's in here when it opens. All right, so these fields might not show up until you close this front end and then reopen it again. Okay, so now let's go into our code that handles shutting things down. Let's go into our code window behind the main menu here. Alright, where is it at? There's our form timer event. This is where all the magic happens right in here all right this guy is basically every whatever seconds you have it programmed to, whatever the timer interval is is checking to see if a pass, a start-up, or into is something; in addition to or you can even replace the city, want to, but will do, in addition to this so I'm gonna create a new subroutine and I might call it uh... check for blackout. Alright, I'm gonna look up that blackout_start_time and blackout_end_time from the table. I like to store those in local variables; it just works better, trust me. So here we'll go then oh here we'll do it BO start as a date I would say that was sometimes around the age of 13 DON as another date. Okay, status, checking for blackout. This is just for you. You don't have to put that in there if you don't want your users to see it. Right? And now a couple of DLOOKUPs. DOSTART equals DLOOKUP("blackout_start_time" from the serverT). And then we'll look up the blackout_end_time as well. DON equals the end time. Now, normally if these were values that the user could mess with, I would put an NZ around there to handle null values, but this is an admin-only table, so you're responsible to make sure there's a value in there. Even if you don't want to have a blackout time, just have it so start time is 5 o'clock, end time is 5 o'clock, or even have it so that the end time is less than the start time. Oh no, actually that will, that'll probably keep you out the whole time. Now that I'm thinking about it though, the way that this is working and the way that I have it coded, your blackout time cannot span midnight. All right, so just keep that in mind. That's a limitation. You're going to see why in just a second here. So if BO start is less than, you can do less than or less than or equal to, it's up to you now, then it's time to do some blackout. So status, blackout time, and then however you want to handle it here, I'll just open up the shutdown form. So do command dot open form shutdown, shutdown F. It's the same thing we did in the last class, end it. Now, one problem you have here though, this is a time like 4 PM or 5 PM or 1 AM or whatever. This now is a complete date-time value with a date on it. Okay so you can't just use now here. What we're going to use is the time value of now. That's going to look at the now, it's going to look at the date time of right now and just give you the time. It ignores the date. There's also a function called date value. Alright, I think I got separate videos on both of these. Yes, I do. I got a separate time value and date value videos. I'll put links to these down below. Alright, so we need the time value of now here and the time value of now on this side. What this is saying is our start time and our end time. We got BO start, BO end. If the start time is less than whatever the now value is, so if BO start is 5 PM add and if right now it's 5:05 PM and the end time let's say is 6 PM and this is still 5:05, then we're going to do blackout. That means you can't open the database. If it's 9 PM then it doesn't fall within those ranges and you're good.
And once again, just keep in mind with this coding, the way it is right now, your blackout time cannot cross over midnight. Because if your start time was 11 PM and your end time was 1 AM, then midnight would not work because it wouldn't fall in there. The time value of midnight would be less than the O start, so it's not going to work. So that's just one little limitation. Could you code something else to get around the AM streak? It could be a little more complicated, but I think this will work for most people. I think most people, if you want an hour for backups or whatever, usually people do that at like 3 AM. Or if you're doing a maintenance window, usually it'll be 9 PM to 11 PM. Anybody that's up at midnight doing database maintenance? Eh. Hm. I've been there, though. I can't lie. I've been that guy having to do database maintenance at midnight. It's not fun. What you would do in that case is just have a situation where you'd look at the values and you'd say, okay, if the end time is less than the start time, then you just reverse that. And you'd look at the other way. So it's more complicated. If you guys really, really, really want to see me write out all the code for that, let me know. I'll put something together. But I think this will work for 99% of the people. Now, you could also make a separate shutdown form, too, because our shutdown form, what it is right now if you look at it, it just says the database will shut down in one minute. You might not want to even have them in the database for a minute. You might want to even have them, you know, it shuts down in five seconds. Just enough for them to see the message, like nope, we're in blackout time, bye. Right, because that could, if the database is open, it could mess up your backup. So you can make a second shutdown form or you could send the text of this to the shutdown for and even change the timer parameters. In fact, we're gonna do that in the extended cut. Alright, we're going to add some embellishments to this. Okay, but for now, we can just use a different startup form or use a shutdown form, whatever you want to do. I'm easy. Okay, so are we ready to test it? Let's see here. Oh, we got to check for the blackout. We wrote our code to handle this. Now, where are we going to put this? We got to call it from somewhere. I'm going to call it from the form timer event, so every time the form timer runs at whatever interval you got this set to, once a minute or once every 10 minutes or whatever. So all you have to do now is say check for blackout and it will handle everything else. We can put that right there. And I'm also going to put it in the form load event. So when the database first loads up, assuming of course we're in your main menu and your main menu is your startup form or at least loads when the database loads. So we'll check for blackout there as well. And yes, one more note, I do recommend that you de-lookup these each time this runs, even though it takes a millennium. You could load them up once when the database starts, but this allows you, the server manager, the admin, to change this on the fly. You know, if it's 7 o'clock and you're like, oh, crap, I got a bug I gotta fix and it's critical, boot everybody out right now, you can just go in here and just change this on the fly. And this way, every time this loads, it will check this. Okay, so I would not cache these values. Alright, so let's test it. Save this. I'm going to, let me see, what have I got my timer interval set to here? Event, alright, this is 10 seconds. Again, you'd make this something like a minute or 5 minutes or 10 minutes. I'm going to make it 5 seconds just for the purposes of the class, so we don't have to sit here and wait for it. And then what's going to happen is that's going to open up the shutdown form which is also set to ten seconds, only that five seconds as well so that's not something quick. All one of the things I do want to do is I'm going to change the shutdown form to a pop-up for now. Usually, I don't like a pop-up forms as if especially the multi-monitor setup they can appear anywhere but at least this will prevent them from appearing behind an existing form if the users are working in here. It could even appear behind the main menu and they won't see it. So at least the pop-up is going to appear on the screen somewhere. It should be here. I've got a whole separate video on how to control where pop-ups pop up using code. It's my move size video. Go watch this. You can control where the pop-up window appears. But I was playing with this guy earlier and it was like appearing behind the main menu. I'm like, where is it? It's not showing up. Another thing you might want to do is make it bigger too so it comes in nice and large. Let's give it a test. We should be ready to test it now. Now my times are set to 6 and 8 and it's currently 6:52 PM so we should be in blackout time. Let's simulate the database opening by me opening up the main menu. Now remember, I have code that I put in here that doesn't shut the database down if it's me, if it's my username and my computer log on as a box. Okay, so it won't actually shut down. But actually, you know what I'm going to do real quick? I was thinking about this earlier. I'm going to take this, because I call it in a couple of different places, I'm going to make a global function for this. I'm going to put that code in there so if it's me it will just display a message and if it's everybody else it'll quit. Alright, I can't use status box here because status box is on the main menu but I can say forms main menu F status box equals right admin mode, the database will shut down here. Just so that I know it's working, but as a developer, I don't want to have to deal with testing my code and then the database closing every time. So I just want to see that menu, but for a normal user, that won't happen. OK? Now I'm just going to replace everywhere that this code appears with do shutdown. So I'm going to go back over here and say do shutdown. And where else does it happen? Let's see here. That happens just here if they close the main menu and it also happens in the shutdown form. It happens, let's see, form timer. Okay, when the timer hits, it's going to do shutdown and if they close this form, it's going to do shutdown. Alright, because this guy's got a timer. When this timer runs out, the database closes or if they close this manually, that will happen. Alright, so I've covered all the bases. Ready to test it, finally this time. You ready? Alright, we're going to simulate the database opening by opening up the main menu. Ready? Go. And boom, immediately. This database will shut down in one minute. Now that's just the standard message. Okay, and you can see in the background here, look, admin mode, the database will shut down here. So it would have quit if it was a normal user. Okay, let me... This is one of the other things you got to close these down fast, as the admin. If you're not an admin, it will shut the database down. All right, let me go into do shutdown and I'll prove it. Here, watch. We'll just, we'll just rem out this stuff. Okay, so when it does do shutdown, it's going to quit immediately. Alright, ready, go. And then database will shut down in five seconds, really. So wait five seconds and there we go. Everybody shut down. See? If I try to open it back up again, nope, sorry, shut it down, five seconds. You can make that one second. You know, just put blackout real big. Now we want to end it. We just go in as admin. And of course, we're admin, so it won't just shut the database on us. But I'll open up the back end and I'll just change the blackout time. So let's say 6 o'clock. OK, it's currently 6:57, so I'll change this to 6:30. So we're past our blackout window. Alright, and now if I open the database... Come on, man, these newer versions of Access got slower. And we're good. See, it's just checking for blackout, but we're not in a blackout time. And there's the regular check for shutdown. That, the old check for shutdown runs at the end of this timer's cycle. Whereas checking for blackout happens when we start the database. Alright, in the extended cut for the members, we're going to do two more things. We're going to specify that shutdown message and the timer interval for the shutdown form. So you could either say, you know, the database is going to shut down for maintenance in one minute and give them a minute. Or you can say the database is currently in blackout, you can't log on, and it's going to shut down in three seconds or whatever you want the message to say and the timer interval and using the same technique you can control the color and the size and whatever you want of that form and we're gonna go one step further I know that from experience not everybody runs a maintenance routine everyday you might only do maintenance and backups twice a week, Tuesdays and Fridays or whatever so we're gonna specify which days of the week the blackout time is valid. And if you want it every day, fine, just put every day in there. But you can do just Sundays at 3 AM. That's all going to be covered in the extended cut for the members. Silver members and up get access to all of my extended cut videos. Gold members can download these databases that I build in the TechHelp videos. And you've seen how valuable that is. Because the gold members are just going to go and get the other database that I built in the last series. So what are you waiting for? You want to join right now? Click that join button. And if you like learning with me, I've got tons of developer lessons and seminars and templates and all kinds of stuff on my website. Check them out. There's a link right there. But that is going to be your tech help video for today. I hope you learned something. Live long and prosper my friends. I'll see you next time.
TOPICS:
Setting blackout time for multi-user databases
Ensuring users are logged out during maintenance
Preventing login during specified blackout periods
Database design using front end and back end separation
Creating new fields for blackout times in server table
Coding to check and compare current time with blackout
Handling database operations based on blackout times
Using timer events to apply blackout conditions
Making dynamic blackout time adjustments as admin
Testing and validating blackout functionalities in real-time
Creating and managing user notifications during blackout
Option for complete shut down during blackout periods
Handling pop-up forms for shutdown notificationsQuiz Q1. Why would you set a blackout time for a database?
A. To perform system upgrades without user interference
B. To prevent unauthorized access at specific times
C. To increase the database loading speed during off-hours
D. To conduct employee training sessions
Q2. What does the blackout period prevent users from doing?
A. Changing their passwords
B. Logging into and using the database
C. Downloading data from the server
D. Sending emails through the database interface
Q3. Which type of data type is used to store the blackout start and end times in the database?
A. String
B. Integer
C. Boolean
D. Date-time
Q4. What limitation was mentioned about the blackout time implementation?
A. It cannot be disabled once enabled
B. It cannot be set for longer than 24 hours
C. It cannot span across midnight
D. It can only be set by an administrator
Q5. What function is used to compare the actual time with the blackout times?
A. TimeCompare()
B. DateValue()
C. Now()
D. TimeValue()
Q6. What happens if the current time falls within the blackout range?
A. The database allows read-only access
B. The database logs out any users and prevents login
C. The database sends an email to the admin
D. The database performs a self-check routine
Q7. Where is the blackout check code called from in the system?
A. From the system startup script only
B. From a dedicated blackout monitoring tool
C. From the form load event and the form timer event
D. From an external server monitoring application
Q8. Can admins customize the message shown when the database enters a blackout time?
A. Yes, they can customize both the message and the timer interval
B. No, the message and timer interval are fixed
C. Yes, but they can only customize the message, not the timer interval
D. Yes, but they can only customize the timer interval, not the message
Answers: 1-A; 2-B; 3-D; 4-C; 5-D; 6-B; 7-C; 8-A
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 covers how to set a blackout time for your Microsoft Access database. This feature is particularly useful for those running a multi-user database environment, where users might log in or out at varying times throughout the day. The goal here is to ensure that, during a specific window each day (for example, from 9 to 9:30 p.m.), all users are forced out of the database for regular maintenance, and no one is allowed to log in during that period. If someone is still logged in when the blackout time starts, the system will kick them out. If someone tries to log in during the blackout window, they'll be notified that the database is unavailable and to return later.
This topic traces back to a previous discussion on the "Force Shutdown" process, where you could set a specific date and time for planned maintenance, forcing everyone out at that moment. Unlike that scenario, the blackout period we are discussing today focuses solely on time, not the date. That means, for example, every day between 2 and 3 a.m., the database will close to all users, both kicking out anyone currently connected and blocking new logins.
To get started, I'm building on the example database from my earlier "Force Shutdown" series. That database is already set up with a split design, separating the front end (which users interact with) and the back end (which stores the data). In our previous demonstrations, we simulated multiple users by running several copies of the front end.
To implement blackout functionality, the first step is to add two new fields to the server table in the back end. These fields are "blackout start time" and "blackout end time." Both are date/time data types, but you'll only be storing the time portion. For class purposes, you might set the blackout window from 6 p.m. to 8 p.m., but you'll use whatever window fits your maintenance schedule.
A key tip here is that, when working with split databases, you should always close the front end before making table design changes in the back end to ensure the front end can recognize any new fields the next time it loads.
Once the blackout time fields are in place, you need to update your shutdown-checking code in the main menu of your front end. The main menu uses a Timer event to regularly check if a forced shutdown is needed. We'll add another check to see if the current time falls within the blackout period. You'll want to call this new blackout-checking logic both in the Timer event (so it checks periodically while the database is in use) and also as soon as the form loads. This way, users are blocked right from the start if it's blackout time.
When writing this code, retrieve the blackout start and end times from the server table using Dlookup and compare them with the current time. Be sure to use only the time portion of the current moment, which can be extracted with the TimeValue function. This way, the comparison ignores today's date and just checks whether the current time falls within the blackout window.
A limitation to note: with this basic setup, the blackout period cannot cross midnight. For example, you can't have a blackout from 11 p.m. to 1 a.m., because the comparison logic won't handle that wraparound. If that is something you need, it would require a more complex logic structure. However, in most real-world situations — like backups or evening maintenance — blackout windows usually don't cross midnight.
During a blackout period, you can simply open a shutdown form that notifies the user that the database is going offline. You may want to adjust the form's timer interval so users are only shown the message for a few seconds before being ejected. In my tests, I recommend setting the timer interval very low while developing, just to speed up the process. In production, a minute or two between checks is fine.
Another usability tip: consider making the shutdown notification a pop-up form so it doesn't get lost behind other open windows. You can even expand the size of this form to make sure the message is prominent, and there are advanced techniques available for controlling the form's position on multi-monitor setups.
When handling this in your code, you might find it helpful to use a global function to manage both checking for blackout periods and handling the shutdown process. If you're an admin or developer and want to bypass these checks for testing, you can build logic to recognize your own login and prevent the database from shutting down unexpectedly while you debug.
As an administrator, it's important that the system checks blackout start and end times every cycle instead of caching them. This allows you to adjust blackout windows on the fly, at any point, without requiring users to restart the database.
After implementing your logic, be sure to thoroughly test the system by setting a current blackout period and simulating regular user behavior. The system should immediately notify users when they enter a blackout window and prevent any logins until the blackout ends.
In today's Extended Cut for members, I'll cover how to customize the shutdown message and the timer interval for the shutdown form. This means you can display tailored notifications, control exactly how long users see the message, change the color and size of the shutdown form, and more. Additionally, I will discuss how to specify which days of the week the blackout applies to, so you can enforce maintenance only on certain nights if necessary. Silver members and above can access all my extended cut videos, and Gold members can download the sample databases used in these lessons.
If you want additional developer lessons, seminars, or templates, those are also available on my website. The complete video tutorial with step-by-step instructions for everything discussed here can be found there as well.
Live long and prosper, my friends.Topic List Setting blackout time for multi-user databases
Ensuring users are logged out during maintenance
Preventing login during specified blackout periods
Adding blackout start and end time fields to a table
Implementing blackout logic in the form timer event
Checking current time against blackout window
Handling blackout restrictions without spanning midnight
Displaying custom shutdown forms during blackout
Using DLOOKUP to retrieve blackout times
Testing blackout functionality with different timer intervals
Configuring pop-up forms for shutdown notifications
Allowing admin override for blackout mode during testing
Placing blackout checks in form load and timer events
Enforcing database shutdown for non-admin users during blackout
Making real-time changes to blackout times as an admin |
