Compact VBA
By Richard Rost
3 years ago
Compact a Microsoft Access Database with VBA
In this Microsoft Access tutorial, I'm going to teach you how to compact and repair your database using VBA. We'll discuss both the CompactDatabase and CompactRepair methods. I'll also show you how to back up your original file and check to see if the compact was successful.
Pre-Requisites
Links
Members
Other Videos
Templates
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, How to compact and repair a Back-End database, How to Compact and Repair Microsoft Access Database Files, Application.CompactRepair, DBEngine.CompactDatabase, compact with vba
Subscribe to Compact VBA
Get notifications when this page is updated
Intro In this video, I will show you how to compact your Microsoft Access database using VBA, including how to add a button to your front end database to compact your back end file. We will cover both Application.CompactRepair and DBEngine.CompactDatabase methods, discuss handling password-protected back end files, and go through important steps like making backups and renaming files. If you manage split database solutions and want to automate compact and repair tasks with VBA, this tutorial will walk you through the basics and highlight some key differences between the available methods.Transcript Welcome to another Fast Tips video brought to you by accesslearningzone.com. I am your instructor, Richard Rost.
In today's video, I am going to show you how to compact your database using VBA. Since we're using VBA, this is a developer tip. If you've never done any VBA programming before, go watch my intro to VBA lesson. It will teach you everything you need to know about VBA programming in about 20 minutes to get you started. Don't be scared of VBA. It's not scary.
If you don't know what compacting and repairing your database is, then this video is not for you. Go watch this video. It's my older compact and repair video. I'll teach you exactly what compacting and repairing is, what it's all about, why it's important, and why you should do it on a regular basis. Go watch this first and then come back.
This video is more geared toward people who have a split database solution where you have a front end and a back end. If you only have one database file, that's pretty easy. Just go to Database Tools, Compact and Repair once a week or so, and you're done. But it's a pain if you've got a bunch of back end files that you have to compact as well.
In this video, I'll teach you how to make a button where you can click it and it will compact your back end file. If you don't know what I'm talking about as far as back end database files go, watch this video. I'll put links to all this stuff down below. They are all free. They're on my YouTube channel. They're on my website. Go watch them and come back.
When it comes to compacting your database with VBA, there are two methods you can use. There's Compact Repair, which is the older one, and there's Compact Database, which is the newer one. First thing you have to do is make sure everyone is out of the database. This will not work if the database file is open. Schedule this to go off at, for example, Sunday at 4 a.m. when no one is in the office, or run around and make sure everyone is out of the database.
Now, Application.CompactRepair has been around for a while. The advantage is it is actually a function, so it will return true if it's successful and false if not. You can handle that accordingly. The disadvantage is it does not work with password-protected files. If you have a password on your back end, then you cannot use Compact Repair. You have to use the second method, which is DBEngine.CompactDatabase.
This will work with password-protected files. In fact, you can even use this to change the password. But it does not return an error level. You can do error handling around it and trap an error, then do something accordingly. One of the reasons I like Compact Repair is it just gives you a true/false if it's successful or not.
This is the newer one, and I've actually seen with some instances of corruption where you're using Compact Repair to repair the database, Compact Database can fix problems that Compact Repair could not. They are slightly different technologies. For the purposes of this video, we're just talking about the compacting part, because repairing is something you hopefully never have to do.
On my system, I have my TechHelp free template as my front end. I've got this set up as a fake back end. We're going to pretend that's my back end file, the one that I'm compacting. Your front end you can just compact whenever you want to because you're using it.
The back end, people always forget about it because it sits on the server and no one ever touches it. It's a pain to sometimes remember to go in there once a week or once a month and compact it, so you put a little button in your front end.
When I open this up, you'll see I made myself a button right here. Let's take a look at the code in the button's design view. Right click, build event.
The easy part of all of this is the compact and repair. All you really need is that right there: Application.CompactRepair, source, destination, and then false. This third parameter is for logging. If you want to log anything that it does, any errors and such, you can. I almost never do that.
I have a couple of variables up here, source and destination. There's my source file. Now the destination - the thing with Compact Repair is it can't compact repair the file onto itself. So what you actually have to do is compact into a new file, then delete the old one, and then rename the compacted one.
The compact part is the easy part. It's all the stuff that goes with it that can be tricky. The first thing that I do here is back up the original file. Always, always back up your file before doing a compact and repair. Right, this guy, age old slide to remind you to back up your stuff. You should be running backups every night. If not, go watch my backup video. There's the link right down there. Go watch it.
This line will use FileCopy to copy the source file to a backup file. This backup file is going to be the same name as the source file, just with .backup and then the date and time formatted like that, yyyy-mm-dd, etc. Whenever you run this, you're going to get an extra copy of the backup file, so you have to remember to go in and delete these, otherwise they will start filling up your hard drive.
You can just call it .backup.1, .backup.2, whatever you want. That's up to you.
Check to see if that destination file (the compacted one) exists. If it does, you have to delete it. The only way that should exist is if it failed the previous time and just didn't delete itself or didn't get renamed. We're going to rename it in a minute.
Then perform the actual compact. Compact Repair will return a result, which is a Boolean. Then we check to see if it's good. If result is true, we have a success, so we kill the source file - that deletes your original file. Be very careful. Then we'll rename the destination as the source. In other words, take the new compacted database and call it what the original back end file was.
That's my status function success; just put the status in this little status box here. If not, if this is not good, then put in there "fail." The original file should not have been touched, so you should be okay at that point.
Now if I come back out here, save it, let's open up the main menu, give it a run, click, just doing its thing. It happened really fast, and it deposited these on my other screen because this is not my primary monitor. As you can see here, now I've got a new copy of my back end database, which yes, it compacted correctly, and I got this backup file.
If you have to restore this, just get rid of everything after the ACCDB and then Access will recognize it as an Access file. It's just putting a time date stamp on the extension there. If you're sure everything is good, you can delete these files; you don't need those around.
That's pretty much it. Now you can compact the back end. If you have a password-protected back end, you have to use a different method. You can't use Compact Repair.
Let me remark all this stuff out here, because you can't return the result either. What you have to do in that case is use DBEngine.CompactDatabase. Source name is still your source, destination is still your destination.
Then you have destination locale, which you can use to specify the location and some other stuff. I'll put a link down below to Microsoft's website. They tell you all those different options for locale. You have Arabic, Cyrillic, Czech, and all this different stuff.
You use the locale parameter for specifying the password. Why they did it this way, I don't know. You can see here they specify the password. It's got to be after a semicolon and then pwd= and whatever your password is.
In here, you can leave destination locale alone and options alone. There are all kinds of other options that you'll probably never use.
Source locale is where you put the password for the source file. It will be ;pwd=whatever your password. And that will then compact the database with that password and it will keep the password in the new database. You can actually change that password by doing this and copying it into the destination locale parameter and changing it. That will now change the password, or you can remove the password if you do that. It's weird. I know. I did not make it; I'm just telling you how it works.
The downside is this is a method - it's not a function so it does not return a value - so you have to wrap this inside of error handling if you want to see if it throws an error, and then you can do stuff accordingly. If you want to learn more about error handling, there is a video for you.
This is primarily why I don't usually use passwords on my back end database, because all of my important stuff, my customer details and order information and anything with credit card numbers or sensitive stuff, I store in SQL Server which is secure. The only back end files I have are unimportant things like email lists and that kind of stuff. It's not really a big deal if it gets in the wrong hands.
If you do have a password on your database, you have to use that method. I'll leave that in there so I put this in the code vault that you guys can see the other line, but that's what I like to do. This one just seems to work smoother too. Maybe it's just me.
Now, what if you have a whole bunch of back end files, maybe 10 or 15 of them? I have a lot of them because I put different tables inside of different files. That's the way I've always done it. You can loop through all the files in the folder and compact them individually. I cover how to do that in my Access Developer 31 class, where we actually loop through all of the files in the folder and then I actually show you how to compact those back end files. I also cover those functions like Dir, FileCopy, Kill, and all that cool stuff.
I also just released this template called the Access Compactor that does all this stuff for you. It's all coded in. You can have it so it will automatically start. You can launch it from your existing front end database. When it opens up, it will compact all the files in your back end, make backup copies of them, shut down when it's done, write a log file out, and then launch your original database back. That's great if you've got a front end that runs 24-7 doing stuff like mine does.
So that's the Access Compactor template. Check it out. It's on my website. I'll put a link down below.
There's your fast tip for today. As you can see, compacting your database using VBA is pretty simple. You really only need one line of code. It's all the stuff that goes with it that can get a little complicated: making copies, making backups, renaming stuff, looping through files, and all that. The actual compact itself is easy; it's the packaging that is sometimes tough.
I hope this video helped. I hope you learned something, and we'll see you next time.Quiz Q1. What is the main purpose of using VBA to compact an Access database as explained in this video?
A. To automate the compacting of back end files in a split database setup
B. To upgrade the Access front end to a new version
C. To import tables from Excel automatically
D. To secure the database with encryption
Q2. Why is it important that all users are logged out of the database before running a compact operation via VBA?
A. Because other users might change settings
B. Because compacting will not work if any user is in the file
C. Because compacting will only add new tables for each user
D. Because the VBA code will automatically log users out
Q3. Which method is recommended for compacting a password-protected back end database?
A. Application.CompactRepair
B. DBEngine.CompactDatabase
C. DoCmd.RunMacro
D. Application.RepairDatabase
Q4. What major limitation does Application.CompactRepair have compared to DBEngine.CompactDatabase?
A. It cannot compact front end files
B. It does not work on password-protected files
C. It cannot handle more than one file at a time
D. It does not copy or rename files during the process
Q5. What does Application.CompactRepair return after execution?
A. The number of records compacted
B. A Boolean indicating success or failure
C. A string with an error message
D. The database password
Q6. Why is it recommended to always back up your database before compacting and repairing it?
A. Because compacting will always delete the original database
B. To prevent data loss in case the compact operation fails
C. To reduce the file size more effectively
D. Because Access will prompt for backup automatically
Q7. When compacting a back end file using VBA, why do you need to use a separate destination file for the compacted database?
A. Because Access cannot open the database after compaction
B. Because Application.CompactRepair cannot compact the file onto itself
C. Because VBA cannot access the same file twice
D. Because you need to keep the source and destination the same at all times
Q8. What additional step is necessary after successfully compacting a database to a destination file?
A. Delete the destination file
B. Rename the destination file to the original source file name
C. Export the tables to Excel
D. Add a new password to the file
Q9. Which feature can DBEngine.CompactDatabase offer that Application.CompactRepair cannot?
A. Logging errors as a text file
B. Changing or removing the database password during the compact process
C. Displaying a message box upon completion
D. Compacting multiple files automatically
Q10. What does the video recommend regarding managing multiple back end files in a folder?
A. Rename all back end files before compacting them
B. Loop through all files in the folder and compact each one individually using VBA
C. Only compact the largest file
D. Combine all back end files into one before compacting
Q11. What is a key reason given for not using passwords on back end Access databases when storing sensitive information?
A. Application.CompactRepair only works with passwords
B. Sensitive data is stored in a more secure database solution like SQL Server
C. Passwords are too simple to remember
D. Passwords can be shared among all users
Q12. What does the Access Compactor template provide?
A. A manual on VBA programming
B. An automated solution that compacts all back end files, handles backups, and logs actions
C. A security audit tool for Access databases
D. A report generation wizard for Access
Q13. Where do you specify the password for DBEngine.CompactDatabase when compacting a password-protected database?
A. In the destination file name only
B. In the source locale parameter, after ;pwd=yourpassword
C. In the Application.CompactRepair logging parameter
D. In the Logon dialog box at runtime
Q14. Why might you need to manually delete old backup files created during the compact process?
A. Because Access will overwrite them automatically
B. To prevent your hard drive from filling up with unnecessary backup files
C. Because backup files are required to run Access
D. To avoid duplicate table errors
Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-B; 7-B; 8-B; 9-B; 10-B; 11-B; 12-B; 13-B; 14-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 video from Access Learning Zone focuses on how to compact your Access database using VBA. This is a developer tip, so it helps to have some familiarity with VBA programming. If you are new to VBA, I recommend starting with my introductory lesson, which covers the basics you will need in about twenty minutes. Many people find VBA intimidating, but it is actually much more approachable than it seems.
If you are not sure what compacting and repairing a database means in the context of Access, this video may not be the best starting point. Instead, I suggest watching my earlier lesson dedicated to explaining the concepts behind compacting and repairing. That video addresses what these processes accomplish, why they are crucial for database maintenance, and why you should make them a regular part of your routine database management.
This tutorial is aimed at users who have a split database setup, where the front end and back end are separate files. If you are working with a single database file, you can simply use the Compact and Repair command under the Database Tools tab periodically—typically once a week is sufficient. However, for those managing multiple back end files, automating the process is a huge advantage.
In this lesson, I show how to add a button in your Access interface that will compact your back end file with a click. For anyone unfamiliar with the concept of back end database files, I have additional resources available on my site and YouTube channel to help bring you up to speed.
When you want to use VBA for compacting a database, there are two primary methods available: CompactRepair and CompactDatabase. Both have advantages and limitations, and your choice depends on your particular setup.
Before beginning the compact process, it is critical that all users are logged out of the database. Attempting to compact a database file while anyone has it open will not work. In a business setting, you might schedule the compact process for a time no one is in the office, such as Sunday at 4 a.m.
The CompactRepair method has been available in Access for a long time. It acts as a function, which means it will return true if the compact operation is successful and false otherwise. You can use that result in your code to take further action as needed. However, CompactRepair does not work on password-protected files. If your back end is password-protected, you have to use CompactDatabase instead.
CompactDatabase allows you to compact password-protected files and even change their password during the process. However, unlike CompactRepair, it works as a method rather than a function—it does not return a success or failure value. To know if it worked, you need to wrap the code in error handling. In my experience, CompactDatabase can sometimes fix certain issues that CompactRepair cannot, so it is worth knowing both.
For demonstration purposes, I use my free TechHelp template as the front end and simulate a typical back end file. Compacting front end files is straightforward, but most people forget about the back end because it sits on the server and is rarely touched directly. Automating the compact process with a button helps ensure you do not overlook this important maintenance step.
Creating the button involves adding code to its click event. The main line of code involves specifying the source and destination files for the compact process. Note that you cannot compact a file onto itself; you have to compact the database to a new file, then replace the original with the newly compacted file. Before making any changes, always back up your database files. I underscore this repeatedly: always make regular backups, and preferably automate nightly backups. I have a separate lesson on that topic as well.
The process I demonstrate includes copying the database file for backup, checking for and deleting any existing destination file (in case of a previous failed attempt), performing the compact, and then—if successful—deleting the original file and renaming the compacted file to replace it. I also include status messages to indicate whether the operation succeeded or failed.
Once it runs, you will have a new compacted database and a backup file with a timestamped extension. If you ever need to restore a backup, simply remove the extra extension and Access will recognize it. Remember to occasionally clean up your backup files, or your hard drive could fill up over time.
For password-protected databases, the process changes. CompactRepair will not work, so you must use DBEngine.CompactDatabase. With this method, you specify the password in the parameters using a semicolon and pwd=yourpassword. Microsoft provides details on additional parameters for different language locales, but most users will never need those options. Changing or removing the password is also possible by adjusting the parameters accordingly. Since this method does not return success or failure, you have to use error handling to manage any issues that might come up.
Because of these limitations, I generally avoid putting passwords on my Access back end files. Sensitive data like customer records or credit card information should be stored in a secure database platform such as SQL Server. For less sensitive data, leaving the file unprotected keeps maintenance much simpler.
If your database setup uses multiple back end files, you can use VBA to loop through and compact each one individually. I cover these techniques, including looping through files and handling file operations, in detail in my Access Developer 31 class.
To simplify the process even further, I have released the Access Compactor template, which streamlines and automates all of these steps. The template can automatically begin compacting all your back end files on startup, create backups, write out a log file, and then relaunch your original database—all without manual intervention. This is ideal for environments where your front end needs to run continuously.
In summary, compacting your Access database with VBA is straightforward, requiring little more than a single line of code for the core function. The supporting operations, such as managing backups, deleting and renaming files, and handling multiple back end files, are where most of the work lies.
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 Compacting an Access back end database using VBA
Creating a button to trigger compact in the front end
Using Application.CompactRepair method in VBA
Creating source and destination file variables in VBA
Backing up the original database file with FileCopy
Generating time-stamped backup filenames in VBA
Checking and deleting existing destination files before compacting
Performing compact operation and checking boolean result
Renaming compacted file to original filename
Basic error handling for compact operation status
Restoring the database from backup files
Using DBEngine.CompactDatabase for password-protected files
Specifying source password with the locale parameter
Changing or removing Access database passwords during compact
Implementing error handling with DBEngine.CompactDatabase
Comparing CompactRepair and CompactDatabase methods
Looping through and compacting multiple back end files |
