Restart Access 2
By Richard Rost
2 years ago
Restart MS Access Database at Regular Intervals Part 2 In this Microsoft Access tutorial, I will show you how to make your Access database reboot itself at regular intervals using batch files, the ping command for delays, and custom pause features. You'll also learn how to set up hourly reboots. This is part 2. MembersThere 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!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, Restart Microsoft Access database, Access automatic reboot, Access timer pause, hourly restart Access, batch script Access, Access database automation, PowerShell script Access, form load event, temp vars in Access, date math Access, Access front end update, Access watchdog, Access updater tool
Intro In this video, I will show you how to make Microsoft Access restart itself by editing a batch file in Notepad, adding a delay with the ping command, and handling issues on fast systems. We will cover how to ensure the database fully closes before restarting, how to add a pause feature to the menu form using a checkbox, and how to control the timer interval based on pause status. I will also show you how to set up scheduled restarts using TempVars and simple date math, including tips for hourly and half-hourly reboots. This is part 2.Transcript Today's part two of my Restart Access series, where I show you how to make Access reboot itself. If you haven't watched part one, go watch part one and then come on back.
Alright, so let's start in our batch file because I want to address this problem that I had initially. Let's edit in Notepad. The trick here is if you got a fast system, right, which my other machine is pretty fast, the Access database will launch the batch file and the batch file will try to run the database before it's shut down. So it'll just think that that's the copy it's supposed to run.
What you got to do is put a little delay in here. Now, there are a lot of different ways to do this. I like to just put a ping command in here, like ping 599cd.com on my website. What that does is Access will run the batch file and then immediately close while that ping is running. That's the first thing that the batch file does. Ping takes about five seconds to run. If you're not on the internet, you could just put in here like ping server or ping your own IP address or whatever you want to put in there.
Okay, I want to ping my server. Go ahead, I don't care. Alright, so I'm going to save that. Now if I run the database, let's open it up. I told you my system's a little slow. There it goes. Alright, it's going to do its countdown for three, two, one. It's going to shell out to the batch file, which is doing its ping. See, it takes about five seconds and then it restarts the database. See? And then this thing's going to do its thing. It's going to loop. And this little delay ensures that the batch file isn't going to start the database until the previous iteration of the database has closed.
If your machine's really slow and you find out that it's still running into a conflict, put two pings in there. Put three pings in there. Put as many pings as you want. Pinging is free, right?
Now, if this is your server, sometimes it's nice to come in here and be able to pause this guy. Maybe you want to stop and go do some work in a field to a table, whatever. So you want to stop that, I'm going to close the menu form. I like to put a little pause on there. So let's go into the main menu form here, design, not delete design. Alright, I'm going to get hello world out of here since we don't really need you. And let's drop a checkbox right under the timer right there. A little format painter. We'll call this guy pause. Alright, we'll justify it there. Let's name this guy pause.
What I'm going to do is when the form loads, we'll make sure pause is set to false. Let's go to the form load event. This guy here unload. Alright, right in here, say pause equals false or true. If you want to start the database, pause, but then your loop won't continue.
In its after update event, find after update event, let me bring this over here. So you can see it there. After update event... and I move things down to my primary monitor screen because that's where the batch file was running. So things are a little discombobulated.
Alright, so in here, we're going to say if pause, then that means if pause equals true, if the user checked on the pause box and it's now on, that means me.timer interval equals zero. That's how you pause the timer. Just set the timer interval to zero. Otherwise, me.timer interval equals a second, put it back the way that it should be. And that's it. Save that. Throw in the debug file. We can close that. We can, let's see here, let's close this and reopen it and pause. Okay, that stops it. And then resume.
You can also, if you want to, put in the pause here, when you click on the pause, set the countdown back to 5 or 30 or 20 or whatever you want to initialize the setting to be. Right. And there we go. There's my little ping and it relaunches. See?
Now, next, you may want this to only run once an hour. I like the reboot. Actually, I used to reboot mine every hour and as my database has gotten more and more complex, I've even started to see some bugs creeping in just an hour. So I actually reboot mine every half hour now, but I'll show you how to do it every hour. It's not that hard.
So in the form load event, where we've been, alright, this unload event. When this form loads, we're going to initialize a start time. You can put it anywhere in here you want. I'm going to use, what is it, Adam that you like to use? What are we going to use? We're going to use temp vars. We'll call it database start time and that equals right now that initializes when the database was started.
You could use a form field variable. You could use a field on the form if you want to. There are a lot of ways you can do this. Now in the form timer, okay, I'm going to assume if you got a countdown timer running, let's say every 30 seconds you want your event to run, then you'll do server stuff here, send your emails, blah, blah, blah, whatever. Now here we're going to check for hourly restart.
I'm going to say if now minus that temp var value, right, this guy right here. Take now, whatever now is, subtract that. What's left over is the difference between those two times. That's a little date math. If that's greater than one, that's a whole day. If this is greater than or equal to one, then that's one whole day. Because in Access with our date math, a value of one equals one day. But I don't want a day. I want an hour. So what's an hour? Well, it's one 24th of a day. That's the easy way to do it.
You could use date diff. You can use all kinds of different ways. This is my easy way to do it. If it's even hours, it's just one divided by 24. You want a half an hour, then divide that by another two. Alright, but I'm just going to leave it like that. Alright, well, if it's that, then do your reboot. There's your hourly reboot. Right here, and if.
Now, the only time the system will restart is if the last time that it restarted was more than an hour ago. And that's how you do that. Then what I did with my database was I wanted mine to reboot as close to the top of the hour and the bottom of the hour. But I'll just stick with the top of the hour for now, as close to the top of the hour as possible.
I know for a fact that my loop takes three minutes to run from the start to the finish. The whole, everything that the server has to do, the longest possible loop will be three minutes. Right? So I added on the end of this. I said, and, right, so this has to be true. This whole thing right here has to be true. It's got to be at least an hour ago. And the minute of now has to be less than 10. So it won't reboot if it's 10 minutes past the hour or later.
So if you started it, you know, if you started the database at 12 after, okay, then it's not going to reboot again until it comes back around to the top of the hour. It's got to be at least an hour since the last time it rebooted. So at most, you might have an hour and 50 minutes. So that's how I did that. But there's all kinds of tricks you can play. This is just more, more legos for your toolbox people.
Now, if you like learning with me, if you like this kind of VB stuff, check out my developer lessons on my website. I've got hundreds of hours of developer lessons to teach you pretty much everything there is to know about Access. I've got a couple of related products to talk about real quick.
I have an Access updater that uses a similar technique to what you just learned, except instead of shelling out to a batch file, it does shell out to another database, alright? The Access updater database and its job are to not only reboot the database, but it also copies the front end around your network. So you set this up so it runs on all the front ends in your office. If you want to push an update once a day or however often you want to run, it will go out to the network, get the update, and then put it on the front end. You don't have to run around to everyone's chairs and update their machines.
I also have this template called the Access watchdog. This guy's job is to watch and see if Access is still running. Okay. If your database locks up, you got a server database doing its thing, sitting in the corner, sending emails, or processing whatever you got to process. If that thing locks up, you want to know about it. Obviously, if it's locked up, it's not going to be able to reboot itself. Well, the watchdog is a little PowerShell script that you also run on the server. It's only job is to watch your Access database and make sure your Access database is running. If not, it kills it and then forcefully restarts it again.
So check that out. I'll put links to all this stuff down below. So that's going to do it. That's going to be your TechHelp video for today. Hope you learned something. Live long and prosper my friends. I'll see you next time.
Hey, special thank you and shout out to our diamond sponsors. First, we have Juan Soto with Access Experts Software Solutions, manufacturing experts specializing in Access and SQL Server. Juan is a 13-time Microsoft Access MVP. You can check them out at accessexperts.com.
Another shout out to Sammy Shama from Shama Consultancy. Sammy is a certified Microsoft Office Specialist and he not only offers Access application development, but he also provides one-on-one tutoring services. If you need someone to hold your hand and help you with your Access project, Sammy is your guide. Check them out at shamaconsultancy.com.
TOPICS:
Editing a batch file in Notepad
Implementing a delay using the ping command
Ensuring batch file doesn't start before shutdown
Using multiple pings for extended delay
Adding a pause feature to the Access menu form
Creating a checkbox to control the pause functionality
Setting initial state for the pause checkbox on form load
Modifying timer interval based on the pause state
Restarting Access database on a timed schedule
Using TempVars to track database start time
Implementing date math for periodic restarts
Conditional logic for hourly and half-hourly restarts
Fine-tuning restart timing to specific intervals
COMMERCIAL:
In today's video, I will show you how to make Microsoft Access reboot itself. We start by addressing the issue with a fast system and using a ping command to create a delay. Next, we add a pause checkbox to control the timer and ensure the batch file only runs the database once it's fully closed. I'll also show you how to schedule the reboot to run every hour. If your system needs a more frequent reboot, I got you covered. Plus, learn about related tools like the Access updater and watchdog. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. Why is it important to add a delay in the batch file when making Access reboot itself?
A. To ensure the user has enough time to save their work
B. To allow the batch file to initiate before the database closes
C. To make sure the previous iteration of the database has completely shut down
D. To prevent unauthorized access to the database
Q2. What method is suggested for implementing a delay in the batch file?
A. Using a 'wait' command
B. Adding a 'pause' statement
C. Creating a loop that lasts a certain duration
D. Using a 'ping' command to an address
Q3. What problem might occur on a fast system without the delay in the batch file?
A. The batch file will fail to launch
B. Access might create multiple instances of the database
C. The database will not recognize the batch file
D. The system will run out of memory
Q4. How can you increase the delay if your system is still too fast?
A. Increase the countdown time in the database
B. Add multiple 'ping' commands to the batch file
C. Use a different server for the 'ping' command
D. Modify the batch file to run in slow mode
Q5. What happens when the 'pause' checkbox in the form is checked?
A. The timer interval is set to five seconds
B. The script stops running permanently
C. The timer interval is set to zero, pausing all actions
D. The database closes immediately
Q6. How do you resume the timer after pausing it?
A. Reboot the database
B. Restart the batch file
C. Uncheck the 'pause' checkbox
D. Press a key on the keyboard
Q7. What timer interval should be set to achieve hourly reboots of the database?
A. 1 minute
B. 24 minutes
C. 1 day
D. 1/24 of a day
Q8. How can you ensure that the database reboots as close to the top of the hour as possible?
A. Measure the loop time and adjust conditions accordingly
B. Reboot the database every 59 minutes
C. Use a fixed interval of 60 minutes
D. Sync the database clock with the server
Q9. Which variable type is used to initialize the start time when the form loads?
A. TempVars
B. GlobalVars
C. SessionVars
D. FormVars
Q10. What value does Access treat as equivalent to one day when working with date math?
A. 1/24
B. 1/12
C. 1
D. 24
Q11. What is the Access updater used for?
A. Detecting faulty databases
B. Updating and rebooting the database, and copying the front end across the network
C. Monitoring access permissions
D. Backing up Access databases
Q12. What is the purpose of the Access watchdog?
A. Checking for updates in Access
B. Ensuring Access database is running and forcefully restarting it if it locks up
C. Optimizing database performance
D. Tracking user activity on the database
Answers: 1-C; 2-D; 3-B; 4-B; 5-C; 6-C; 7-D; 8-A; 9-A; 10-C; 11-B; 12-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 Restart Access series, where I explain how to set up Microsoft Access so that it can restart itself automatically. If you have not yet seen part one, I recommend starting there and returning here once you are caught up.
To begin, let's address a common issue that arises on faster systems. When Access initiates the restart process, it launches a batch file. If your computer is particularly quick, the batch file may attempt to reopen the database before Access has fully closed, resulting in conflicts. To solve this, you need to add a delay to your batch file. There are several ways to achieve this, but one of the simplest is to use the ping command. For example, you can have the batch file ping my website (599cd.com) as its first action. This causes a delay of about five seconds, giving Access enough time to close before the batch file tries to restart it. If you do not have internet access, you can ping your own computer or any other address.
If you discover that five seconds is not enough on your system, you can add more ping commands to extend the delay. There is no cost or negative impact to pinging multiple times, and this flexibility makes it easy to tailor the process to your specific needs.
Next, let's talk about adding a pause function to the database. If you are running your Access database on a server, you might occasionally want to halt the automated restarts so you can perform maintenance or work on a table. To enable this, I recommend placing a checkbox on your main menu form labeled "Pause." When the form loads, set this checkbox to false, ensuring the system is active by default. Through the checkbox's after update event, you can control the timer interval. If the checkbox is checked (pause is active), set the timer interval to zero, which has the effect of pausing the timer and preventing the reboot loop from continuing. Unchecking the box restores the timer to its original interval and resumes normal operation.
You can also enhance the pause feature. For example, when resuming from a pause, you might want to reset the countdown timer to an initial value such as five or thirty seconds. This adds a bit more control over how and when the reboot process resumes.
Another thing you may want is to schedule restarts at specific intervals. Many users prefer to have their database reboot every hour. I reached a point, as my database became more complex, where even hourly reboots were insufficient and I reduced the interval to every half hour. To set up an hourly restart, initialize a start time when the form loads. I recommend using TempVars to record when the database starts. This can also be accomplished with a form variable or other means, but TempVars are convenient for this purpose.
In the timer event of your form, compare the current time to the stored start time. Since Access stores dates and times as fractions of days, a value of one represents a full day. For an hourly interval, use one divided by twenty-four, and for a half hour, divide by an additional two. If the difference between the current time and the stored start time meets or exceeds your chosen interval, trigger the restart.
For fine-tuning, you may want to have your restart occur as close to the top or bottom of the hour as possible. For instance, if your timer loop may span several minutes, you can add a condition that only permits a restart if, for example, the minutes component of the current time is less than ten. This ensures that the restart aligns with a preferred part of the hour and does not occur at a random time.
In my own workflow, this process allows the database to reboot in a controlled and predictable fashion. You can adapt the specifics of the timing logic to match your own requirements.
Before we wrap up, let me mention a couple of related products that may interest you. The Access Updater tool I offer uses a similar method, but instead of using a batch file, it launches another Access database whose job is to distribute updated front ends to all users on your network. This makes it easy to keep everyone up to date without visiting each workstation individually.
There is also the Access Watchdog template. This utility runs alongside your database and monitors whether Access is still operational. If the main database process hangs or crashes, the watchdog will terminate and restart Access, which is especially valuable for unattended operations on a server.
You will find links to these tools on my website.
That concludes today's TechHelp tutorial on restarting Microsoft Access automatically, handling delays, pausing the timer, and setting scheduled reboots. For complete step-by-step video instructions on everything we covered here, visit my website using the link below.
Live long and prosper, my friends.Topic List Editing a batch file in Notepad
Implementing a delay using the ping command
Ensuring batch file waits for Access to shut down
Using multiple pings for longer delay
Adding a pause option to the Access menu form
Creating and naming a pause checkbox
Setting pause checkbox state on form load
Modifying timer interval with checkbox state
Restarting Access database with a timed event
Using TempVars to store database start time
Performing date math for restart intervals
Checking time difference to trigger a restart
Customizing restart intervals to hourly or half-hourly
Limiting restarts to specific times within the hour |
