Restart Access
By Richard Rost
2 years ago
Restart Microsoft Access Database at Regular Intervals
In this Microsoft Access tutorial, I will show you how to automatically restart your database at regular intervals to prevent issues like crashes, lockups, and error messages. We'll cover creating a simple Windows batch file, setting up a countdown timer using VBA, and ensuring your database restarts smoothly.
Brianna from Rockville Centre, New York (a Platinum Member) asks: I have an Access database set up that runs like a server. It sends emails, processes some long action queries, updates my website, and does some other housekeeping chores. I've noticed, however, that after a couple of days, if I don't manually restart the database, I start seeing all kinds of weird behavior. Sometimes it locks up. Sometimes it crashes. Sometimes I get crazy error messages. What can I do to fix this?
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
Recommended Courses
Keywords
TechHelp Access, restart Microsoft Access database, automate Access database reboot, prevent Access database crashes, schedule Access database restart, batch file for Access, create batch file to restart Access, VBA code for Access timer, VBA tutorial for Access database reboot, manage Access database errors, Access memory leaks solution, automated Access restart script, optimize Access database performance
Subscribe to Restart Access
Get notifications when this page is updated
Intro In this video, I will show you how to automatically restart a Microsoft Access database that runs like a server and tends to freeze, crash, or display errors after long periods of continuous operation. We will talk about common causes for these issues, such as memory leaks, and walk through creating a Windows batch file and using VBA code to schedule and trigger regular database restarts. You will also learn how to set up a countdown timer with the TimerInterval property, find the msaccess.exe path, use the Shell command in VBA, and properly exit Access with DoCmd.Quit and acSaveAll.Transcript Today we're going to talk about how to restart Access. If you've got one of those Access databases that just kind of runs and does its own thing on a timer, maybe it's like an axe, like a server on your network. Well, sometimes you got to restart that puppy. We'll talk about it today.
Today's question comes from Brianna in Rockville Center, New York, one of my platinum members. She says, "I have an Access database set up that runs like a server. It sends emails, processes some long action queries, updates my website, and does some other housekeeping chores. I've noticed, however, that after a couple of days, if I don't manually restart the database, I start seeing all kinds of weird behavior. Sometimes it locks up, sometimes it crashes, and sometimes I get crazy error messages. What can I do to fix this?"
Well, Brianna, I had the same situation. I have an Access database that kind of acts like a server like yours as it sends out emails. It updates information from the website, pulls down orders, all kinds of stuff. It's constantly running 24/7. I recognize this behavior too. A couple of years ago, if I let it run for, in my case, four or five days, it would lock up. I'd have to log in, if I was on the road, with remote access software and reboot the database. But I found that if I reboot once an hour, that problem goes away. If all this machine is sitting there, just sitting on a loop once an hour, throw a reboot in, it starts itself back up again, and everything's fresh.
Why does this happen? Well, no software is perfect, and Access has its quirks. There's things like memory leaks that happen. For instance, if you declare a variable, for example, and you don't clear it, like an object variable, like a record center or something, Access is supposed to do that kind of stuff behind the scenes. But it doesn't. After your loop runs a few thousand times, these little memory leaks tend to add up. So it's good to just clean the slate. I think of it like a chalkboard. After a while, you can write on it with chalk and use an eraser to erase it, but you still see lots of bits of chalk all over the place. Once in a while, you just have to come in with a wet sponge like a teacher used to make you do and just clear the whole thing. So that's what rebooting does. It just clears the slate.
Now before we get started, this is a developer-level video. What does that mean? That means we're talking VBA folks. So if you've never done any VBA programming before, stop now and go watch this guy. This will get you started. It teaches everything you need to do in about one minute. Also, go watch this video too. This is where I teach you about the timer interval in a form. This way, you can have an event happen every second, every 10 seconds, whatever. We're going to use that for a countdown for our reboot timer. These are both free videos on my website, on my YouTube channel. Go watch those and then come on back.
All right, so let's talk about how to do this. I've created a folder on my desktop called "My DB." That's going to be my makeshift server folder. All right, wherever your database happens to be. And in here, I've got a copy of my TechHelp free template. You've seen this before. I just renamed it My DB for simplicity's sake. Because we're going to need to do something with the file names in just a minute here.
Now the problem that we have is that Access can launch other programs. Essentially, you can use it to launch, you know, Word documents or PDFs or web browsers, whatever. But it can't launch a copy of itself. That's the one issue that we have here. You could make a whole second database where its whole purpose when you start it is to just wait a few seconds and then relaunch the original database. In fact, that's what my Access updater does. I'll talk about that toward the end of the class. But all we really need to do, we don't really need to go through the hassle of making a whole second database. We could just make a little batch file.
A little Windows batch file. I'm going to show you how to do it. It's basically just a command that says, "Okay, I want you to start my database." So here's what's going to happen. The Access database is going to launch this batch file. Close itself. Close the database. And the batch file will restart the database again. It's a real simple process with a couple of tweaks. I'm going to talk about those tweaks in just a minute.
So, let's create: right-click, new, and then text document. Right down here, text document. I'm going to call this restartDB and just leave it .txt for now. RestartDB.txt. That's fine. We're just going to edit it in Notepad. Open that up. Here's Notepad.
Now before we write the batch file, we've got to find where Microsoft Access is. We need the full path to msaccess.exe. If you're not sure where that is, come down to your start button. Type in access. There's Access, the app, right there. Right click on it and go to Open file location.
Now this isn't what we want. This is just a shortcut to it that's sitting on your start menu. But if you right click on that guy and go to Properties, the thing in that Target is the full location to where msaccess.exe is. Copy that to your clipboard with Control C. That's someone's beaming in. Once we've got that in the clipboard, you can cancel that. You can close this.
Come back to your batch file here. You're going to type in start and then inside quotes, paste that location in there. It's already got quotes. You don't need two quotes there. C:\\Program Files\\Microsoft Office\\root\\Office16\\msaccess.exe. That's the program and start just tells Windows, the boss, whatever. I'm old. Start just tells it to start this application.
Then we've got to give it one more bit of information, and that's the full path and file name to your database. That's this guy. That's why I renamed it just to that. So come up here. Get the full path. Copy that. Come back over here. And then in here, you're going to go "path\\MyDB.accdb." Okay, that's the whole command right there. Start Microsoft Access with this database.
Save your text file. Now close it. We're going to rename this guy to .bat now. BAT. That's a batch file. Do you want to rename it? Are you sure? Okay. Now if I double click on this guy, it's going to run that guy. That's all this guy does. Here we go. Double-click. And it starts up. It's on my other screen. There it is, right there. It's launching. There it goes. It takes a second sometimes and there's the database.
All right, it just launched a copy of that database. Let me do it again. It's opening up on my first screen, so I got to drag it up here. It takes a second sometimes. My laptop's old. I've got a new one that I haven't hooked up yet. It takes me at least a good two or three weeks to get a new laptop setup because I got to install my applications and set everything up exactly where I want it. So I do it every two or three years and it's time.
Okay, so there we go. Now we got the batch file that opens up the database. Now we got to make the database run the batch file when it's time. Let's open up the database. This is the TechHelp free template. You can grab a copy of this off my website if you want to. What we're going to do is we're going to make this form have a countdown timer on it. That's just going to simulate the form doing stuff. Whatever your server loop happens to be, sending emails or processing stuff on the website or whatever your database does. We're just going to simulate that with a loop.
So we'll make this the countdown. So this will be countdown. We're going to take this field that we already have on here. We're going to rename this countdown. I'm going to get rid of the control source, get rid of the format. And now we have to initialize this thing and start the timer loop when the form loads.
Go to the form's properties, go to events. We'll start with the Form Load event. So find Form and then Load right there. On Load... and open up our code builder. Here we go. Slide this over here. And in the Form Load event, I'm going to say how many seconds do you want? We'll say five seconds. Countdown equals five. Right. And I'm going to say Me.TimerInterval equals 1,000. Remember these are milliseconds. So every second, the Form Timer event is going to run.
What are we going to do in the Form Timer event? Well, we're going to count down. And then, if the countdown is zero, we're going to issue our restart database. While we're in the form's properties, you can just go right here and drop this down and find Timer. That's the Form's Timer event. This is going to run now every second. So I'm going to say Countdown equals Countdown minus one.
And if Countdown is less than or equal to zero, it shouldn't get less than zero. Wouldn't be an interesting case, right? Then, do some stuff. What's the stuff in here? First, Status = "Restarting database." That's my little status box. That's this guy right there. I covered the Status box function in the video where I showed you how to build this. It just writes to a text box.
Now, we're going to shell out to that batch file that we wrote. Shell is a command built into Access. Yes, there are other commands that are sometimes better like ShellExec and some other ones. Shell is simple. It's easy. It's built into VBA. You don't have to have any dependencies or anything else. Just use Shell. Trust me, it's easier. You do need to have the full path and name of your batch file. So let's go back to that database window right here.
It's going to be this "C:\\path\\RestartDB.bat." All right. So back in here, paste that in and then restartdb.bat. But this whole thing has to be inside quotes. And if you got spaces in this path at all, which you probably do, that has to be inside double double quotes like that. Because the command itself has to be inside double quotes and in order to get two of them in there, you got to put it inside double double quotes. It's a long story. I'll put a link to my double double quote video down below if you have no idea what I'm talking about.
All right. So that's the command to run that. We want to see what's going on though. So I'm going to go comma, vbNormalFocus. The default is vbHide. If you don't specify it, actually, I'm sorry, the default is vbMinimizeFocus. So it starts on the taskbar. But I want to see what's going on. NormalFocus makes it in a normal size window.
Now, this is going to shell out and run that batch file. As soon as it does that, we want to exit the database. So DoCmd.Quit and then acSaveAll. That's just for you. That's in case you've got a form open in design mode and you forgot to save it. If you don't put SaveAll, it's not going to save your changes.
Save it. Give it a Debug Compile. Everything's good. And now let's open it up and see what happens. Five, four... there's the countdown... three, two, one. Zero. Restarting database. Okay. This launched on my other window. Come on. There it goes. It launched. See that? It's going to do it again. Shutting down. Sitting there, relaunching. And there it goes. See?
All right. Couple more enhancements I want to put in here. First of all, if you've got a really fast system, which I don't right now, then when it launches that batch file, it might start trying to load the database before the database has closed the first time around. So we'll put a little delay in the batch file. Like a five-second delay in the batch file. This gives Access time to shut down in the background before it reopens the database. Sometimes you have to have that, especially on a really fast machine. Also, we'll talk about how to delay the reboot until like once an hour. You don't want to reboot it every five seconds. I'll show you how to code it in so you can reboot it once every hour.
And then like you see here on the start screen, we'll put a little pause in here. Sometimes it's nice that even if it's on your server, you want to be able to pause the countdown loop. We'll put that in the code as well. So we'll cover all this tomorrow in part two. So tune in tomorrow, same bat time, same bat channel. Or if you're a member, you can watch it right now.
But 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.
A 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. He not only offers Access application development, but he also provides one-on-one tutoring services. So if you need someone to hold your hand and help you with your Access project, Sammy is your guy. Check them out at shamaconsultancy.com.
TOPICS:
How to restart Access database automatically
Common issues with long-running Access databases
Memory leaks in Access databases
Creating a countdown timer in Access VBA
Setting a TimerInterval for an Access form
Writing a Windows batch file for restarting Access
Finding the path to msaccess.exe
Using the Shell command in VBA
Exiting Access with DoCmd.Quit and acSaveAll
Adding a delay in a batch file
Pausing a countdown loop in Access
Simulating server tasks in Access with a loop
Launching external programs from Access
COMMERCIAL:
In today's video, I will show you how to restart your Microsoft Access database when it runs like a server and starts to act up. We tackle Brianna's issue from Rockville Center, New York, where her database sends emails, processes long queries, and updates her website, but crashes after a few days. I explain why this happens and offer a solution using a simple Windows batch file to restart your Access database automatically, preventing those crashes and weird errors. This tutorial involves some VBA coding, so make sure to check out the intro videos. 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. What is the main purpose of the Access database mentioned by Brianna in the video?
A. To track customer information
B. To send emails, process action queries, update the website, and perform housekeeping tasks
C. To manage inventory
D. To generate financial reports
Q2. What issue is Brianna experiencing with her Access database?
A. It does not open when she double clicks on it
B. It frequently crashes and shows error messages after running for a few days
C. It fails to save the data properly
D. It generates incorrect reports
Q3. According to the video, what can be a cause of the issue Brianna's Access database is experiencing?
A. Incorrect database schema
B. Memory leaks from variables not being cleared
C. Insufficient storage
D. Internet connection issues
Q4. What solution does the presenter suggest for avoiding the problem of Access locking up or crashing?
A. Reinstall Access every month
B. Write detailed logs to identify the issue
C. Regularly restart the database, for example once an hour
D. Increase the available memory on the server
Q5. What kind of file does the presenter suggest creating to help restart the Access database?
A. A .doc file
B. A .xls file
C. A .exe file
D. A .bat file
Q6. What command is used within the batch file to start the Access database?
A. open
B. execute
C. launch
D. start
Q7. How does the presenter suggest locating the full path to msaccess.exe?
A. Searching for it in the Windows folder
B. Right clicking on Access in the Start menu and selecting Open file location
C. Checking the system registry
D. Typing "msaccess.exe location" in a web browser
Q8. What is controlled by the TimerInterval property in the Access form?
A. How often the form updates its control values
B. The time it takes for the database to open
C. The interval between form refreshes
D. The delay between Timer event executions
Q9. What event does the presenter use to create a countdown in the Access form?
A. Form Open event
B. Form Load event
C. Form Resize event
D. Form Unload event
Q10. What Visual Basic for Applications (VBA) command does the presenter use to execute the batch file from within the Access database?
A. Call
B. Shell
C. Exec
D. Run
Answers: 1-B; 2-B; 3-B; 4-C; 5-D; 6-D; 7-B; 8-D; 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 covers how to automatically restart your Microsoft Access database when it is acting as a server and begins to encounter stability issues. If you have an Access database that is running constantly on a network, handling tasks like sending emails, running long action queries, updating your website, or performing other background work, you might have noticed that things start to go wrong after a few days. You could see unusual errors, find that the database locks up, or experience crashes. Fortunately, there is a practical solution for this, which I will explain.
This topic comes from a question by a viewer who has a backend Access database running continuously for server-like processes. She mentioned it starts throwing errors and sometimes locks up if it is not manually restarted every few days. I have personally dealt with the same issue. My own Access-based "server" runs 24/7, and I used to experience crashes or freezes if it was left running continuously for four or five days. Remote access would be required to reboot the database, which is inconvenient and wastes time.
The root of this issue often comes down to memory leaks and resource management problems in Access. For example, when object variables are declared and not properly cleared, such as a recordset or another object, residual memory usage accumulates over time. Access is supposed to handle cleanup, but in practice, it can miss some. These leaks multiply every time the database loops through its cycles, eventually causing instability. You can think of it like a classroom chalkboard. If you use just an eraser, bits of chalk accumulate. Every once in a while, you need a wet rag to completely clear it off. Periodically restarting the database provides that clean slate.
Given these realities, if you schedule Access to restart automatically every hour or so, you can keep it running smoothly. If your setup is just a dedicated "server" loop on a machine, simply restarting once an hour can prevent these problems.
This solution does require a basic understanding of Access VBA. If you are not familiar with VBA programming, I recommend visiting my website where you will find beginner resources. I also suggest watching my video on using the TimerInterval property in Access forms. This will give you the foundation you need for building timed events, which are essential for this process.
To get started, you will need a dedicated folder for your database, which can be anywhere you choose. For this example, I use a folder called "My DB" with a copy of my TechHelp free template database. I kept the name simple to make it easier when referencing the file in commands.
The main challenge is that while Access can launch other programs, it cannot directly launch a second copy of itself. You could create a separate "helper" database for the purpose of restarting your main one, but that adds unnecessary complexity. A more straightforward approach is to create a simple Windows batch file that launches your database.
This batch file is nothing more than a text command telling Windows to start Access and open your database. The workflow is: Access calls the batch file, then closes itself. The batch file launches a new copy of your database. That's all there is to it.
To create this, you simply create a new text file and write a command that uses the full path to msaccess.exe, followed by the complete path to your database file. If you are unsure where msaccess.exe is located, you can find it by searching for Access in the Start menu, right-clicking, choosing "Open file location," and then examining the shortcut's Properties to find the correct path. Copy this path.
Paste this into your batch file, along with the path to your actual database file. For example, your batch file command will specify start, then the path to msaccess.exe, and then your database file. Rename the file from .txt to .bat so Windows recognizes it as a batch file. Now, if you double-click this batch file, it will launch your database.
Now that you have a functional batch file, it's time to trigger it from within Access. Open your database and prepare a form to use a countdown timer to simulate your "server" process. You can reuse an existing field, such as a text box, as the countdown display. Remove any control source or formatting so you can freely manipulate its value via VBA.
In the form's Load event, initialize your countdown timer, such as setting it to five seconds for demonstration. Set the form's TimerInterval to 1,000 milliseconds to trigger your Timer event every second.
Within the Timer event, decrement your countdown each time it fires. When the countdown reaches zero, perform whatever housekeeping you need (such as updating a status indicator). Then, use the built-in VBA Shell command to run your batch file. Remember to provide the full path to your batch file and wrap the path in double quotes, especially if the path contains spaces. I discuss how and why to use double-double quotes in another tutorial on my website, if that's unfamiliar.
To make sure you see the process running, specify the NormalFocus option, which launches the batch file in a normal window. Immediately after calling the batch file, instruct Access to close itself with DoCmd.Quit and acSaveAll to ensure all work is saved.
Test your code by opening the form. You'll see the countdown, and when it reaches zero, your status will indicate the database is restarting. The batch file will run, which closes and reopens your database, keeping your "server" fresh and stable.
A couple of enhancements you may want to consider: If your system is particularly fast, there is a risk that the batch file might attempt to relaunch Access before it has fully exited on the first try. You can add a brief delay in your batch file, such as a five-second pause, to allow time for the process to end before reopening. In future updates to this example, I will show how to schedule the restart for a more reasonable interval, like once an hour, instead of every five seconds. You may also want to include the ability to pause the countdown loop, which is helpful for performing maintenance or investigating any issues that arise.
This covers the fundamentals for ensuring your Access "server" remains stable by automatically restarting on a schedule. In the next part, I will discuss implementing a longer interval and adding a pause feature.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List How to restart Access database automatically
Common issues with long-running Access databases
Memory leaks in Access databases
Creating a countdown timer in Access VBA
Setting a TimerInterval for an Access form
Writing a Windows batch file for restarting Access
Finding the path to msaccess.exe
Using the Shell command in VBA
Exiting Access with DoCmd.Quit and acSaveAll
Simulating server tasks in Access with a loop
Launching external programs from Access |
