|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Compact & Repair By Richard Rost 
6 years ago
Compact & Repair: Why it's Important, How To, How Often? In this video you will learn about the importance of compacting and repairing your database regularly, exactly what compacting does, how often you should compact, HOW to compact, whether you should use "Compact on Close," tips on compacting over a network, and the efficiency of Access even without compacting. Patrick from Miami FL (Gold Member) asks, "My database only has a few thousand records in it, but it’s approaching 1 GB in size. What can I do to make this smaller?" MembersThe Extended Cut covers about 3 hours of me building two databases to test the efficiency of short text, long text, compact & repair, and more. See below for a list of all the topics covered in the Member's video.
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! New
Links
IntroIn this video, we will talk about the importance of using the Compact and Repair feature in Microsoft Access to keep your database running efficiently and prevent file bloat. I will explain how Access handles deleted records, why compacting reduces file size, and when you should perform a compact and repair. You will also learn the steps to safely compact and repair your database, precautions to take, and best practices for regular maintenance, especially on multi-user databases or those stored on a network.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com.I am your instructor Richard Rost. In this video I am going to talk about compacting and repairing your database and why it is important. Today's question comes from Patrick in Miami, Florida, a Gold Member. Patrick asks, my database has only a few thousand records in it, but it is approaching one gigabyte in size. What can I do to make this smaller? Well Patrick, there is a feature in Microsoft Access called Compact and Repair. The repair part of it will fix any problems in your database, but the compact part helps to keep those problems from forming in the first place. As you add and delete records in your database, little gaps are formed between them. Let's say you have a table with four records in it: Joe, Bill, Sue, and Alexander. As I have talked about in previous videos, Access does a really good job of not wasting any extra space inside of that text field. However, if you delete Sue's record, now you have a gap there. Access just leaves it. It marks it deleted, but the data still sits there. That is sometimes how, like when you delete a file on your hard drive, you can still undelete. Access does not reclaim that space. It just sits there, marked deleted. When you add your next record, it gets added onto the end of the table. So as you add and delete, add and delete, you get these gaps and that space does not get reclaimed. So if you add 200 megabytes worth of records and delete 100 of them, and then add another 100 megabytes of records, now you have 300 megabytes of storage space wasted in your database. That is why compacting your database is important, because Access will go through and get rid of all that space from all of those deleted records. Now you are left with a fresh database file that has no empty spaces in it, no gaps. This will keep your database nice and small and efficient. And of course, a small database is a fast database, both on your computer if you are the only one using it, and if it is on a network. If you have to go over a network, Access has to transmit all that data back and forth. Big files mean slow load times. Access can also accumulate some junk over time. If you do not compact the database regularly, you will notice it just slowly gets bigger. There are index files and temporary objects, and Access does not always clean up after itself automatically. So a compact and repair once in a while will get rid of all that junk. The repair end of things, when you do a compact and repair, can sometimes fix small problems before they become big problems. Especially long text fields have a bad reputation for getting corrupted. Sometimes Access can fix a little problem before it becomes a big problem and you lose a lot of data. In older versions of Access, as I mentioned earlier, Access, when you do a compact and repair, would actually get rid of wasted space between your files. If you had phone numbers that are 10 digits long stored in a 255 character long text field, it would get rid of all that extra space. But new versions of Access do not have that problem anymore. How often to compact depends on your usage. Personally, I am the only one that uses my database in my office. My customer database has all my course information in it. I compact maybe once a week. The max size of all of my database files together is about a gigabyte and I have multiple split databases. I have a little routine that I run and it takes about 10 minutes. If you have a database you are using on a network with a bunch of people, you have to make sure that everybody is kicked out of the database, and it depends on how fast your database files are growing. Even over the course of a week, see how big they are getting. Compact them down and know what their file size should be, then watch it after a week and see how big it is getting. You may only need to compact once a month. Newer versions of Access are really good about keeping the database file small. Essentially, the more people using it and the more you are adding and deleting records and creating those holes, that determines how often you will have to compact and repair. How to perform a compact and repair: Let me repeat that for those in the back: always make a backup first. Make a manual backup. I know you probably have a nightly backup routine. Make a manual backup of any file before you compact it. I have seen the compact and repair process ruin the database file. Believe it or not, I have seen it happen. Granted, that was an older version of Access maybe 10 years ago, but still the database seemed to be running fine. I did a compact and repair, and it stopped working. I had to go to great lengths to get it to work right. Of course I say this a lot, but make sure you have daily backups, a weekly backup, a monthly backup. So if you realize you had database problems that crept into your database two months ago, you can go back and restore from those files. Do not just keep three nights of backups. That is not good enough. Next, make sure you have exclusive access to the database. Make sure no one else is logged in. You have to be the only one in the database to compact and repair it. So if this is the database that your whole network runs on, do it on a Sunday when no one is working. Doing this is as easy as opening up the database, going to Database Tools, Compact and Repair. If you want to bypass the startup form, remember you can hold down the Shift key when you open the database. It will bypass whatever startup you have going on. Database Tools, Compact and Repair. Accept it. Access will actually copy your database objects into a new database file and then delete the old one and rename it for you automatically. So you have a fresh, clean file. There is a Compact On Close option. I personally do not use it. I do not like it, but it is there if you want to use it. It is under File and then Options under Current Database. Then there is Compact On Close. Whenever you close this database file, Access will do a compact and repair. If you have a small database, a couple of megabytes, nothing really major, sure, fine. Do a Compact On Close, no big deal. But if you have a big database, if your database is 500 megabytes, one gigabyte or bigger, it is going to take a while to compact. That is why I do not usually do it on close. Plus, unless you are doing a lot of adding and deleting, you really do not have to compact all that often. Once a week is usually fine for most Access databases. Compacting On Close just slows you down. Depending on the speed of your computer or if you are doing it over a network, it could take a while. That brings up my next point. I do not recommend that you compact over a network. Copying files over the network wire can cause issues. I recommend what you should do is copy the database files down to your local workstation, compact them there, and then copy them back up again. It is just less of a chance of any kind of file corruption. I compact over my network all the time, but it is just me. I have three computers in my office, and I have a server and two different workstations. I do not have to worry about someone else accidentally trying to access it while I am in the middle of compacting it. Especially if you are in a network situation where you have 10 or 20 other people on this database, copy the files down, take them offline, do the compact, and then copy them back up again. Yes, you can compact with VBA code, and I will show how in the extended cut of this video. There is basically one line of code you can put in your database to have it compact and repair. You can have a database compact and repair a different database file, which is what I show in the video. This way you could make one master database that will then go and compact and repair all your back end split databases. That brings up my next point: consider splitting your database. If you have a big database file, if your database file is approaching that two gigabyte limit, you may want to consider breaking it up into multiple back end tables. You have your front end that has your forms and your reports and your queries, and then your back end has your tables in it. If that back end file is getting big, if it is getting close to two gigabytes, you may want to break that up into multiple back end files. Have one for customers, have one for orders, have one for order details. I have all my big tables in their own database file because it helps you bypass that limit. Remember, it is two gigabytes per file, so you can have multiple files. There are some drawbacks. You lose things like referential integrity, but by and large, it is good to keep your databases small. Keep the files small. They perform better. It is less traffic going over the network, too. If you want to learn more about splitting your database, I have lessons on that too. I will put links in the description below the video. One of the things I mentioned earlier is that in older versions of Access, the tables themselves, if you did not specify the right field sizes, could cause wasted space in the database. That is no longer true. I actually did some testing on my own. Even without compacting, Access is about 99 percent efficient. Most of the wasted space that comes in a database is because of deletions of those records. I actually built a database to test this. I built two databases. One actually runs the other one. I tested short text, long text, multiple different numbers of records, multiple field sizes, and pretty much after you add records and compact it in almost every case, once you get over a certain size, obviously, Access is 98 and 99 percent efficient. The smaller ones are because the database itself has some overhead. My database empty, as you can see, was 442K. If I only added a thousand one-byte records, it only added a little bit extra, so it was not as efficient. But as you got up to a 160 megabyte database file, it is 99.99 percent efficient. I did this with 255 character fields. I did it with some long text, some random values. So compacting was dead on. Access keeps a pretty good storage of your data without even needing to compact it. The problem again is when you start adding deleted records into the mix. I tested that too. I added 100 megabytes of records, deleted them, and then added another 100 megabytes, and the database file was 200 megabytes. So that is definitely something I tested myself. Now if you want to learn more, like if you want to learn the details behind all the testing that I did to generate that data, the extended cut for members has lots of goodies in it. It is about three hours long. It is me going through the testing process. I just sat down yesterday and started doing this as a kind of a test. I get a bunch of questions from people, and when I get a bunch that are kind of all related together, like I got this one, I got the one about text file sizes, I got the one about short versus long text. I start to see a theme, and I am going to do all these together and build a database and test all this stuff. I learned a certain way of building things back in the 90s when Access was pretty new and it did not do a lot of the stuff it does now. I kind of got used to it being that way. Sometimes things change and you have to stay up to date. I like to be the one that actually tests this stuff myself. I do not like just reading it in a book. I like to get in there, open up the hood, and get my hands dirty. I built two databases. One actually lets you put the counter in, you put how many records you want, how big you want each record to be, and it just adds them, and then the other database controls that one. So it will open it up. I show you how to open up another database. I show you how to do a sleep function so you can wait for that database to exit, compact the database when it is done, read its file size, how big it got. Read and write text files because I use a text file to control the other data. I do not want to actually write to the database itself and put data in the tables because that might affect the outcome. So I use a separate text file. I show you how to read and write text files, delete and rename files, of course. And I show you a recordset, which is what I used to actually add the records to the database. It is a lot of fun. It is a lot of me pulling my hair out because I am testing and experimenting with stuff too. But if you want to see the process behind building a database like this, then it is for my members. My Silver Members and up, how do you become a member? Click on that Join button right below the video on YouTube and you will get access to my extended cut TechHelp. I have a lot of them now. I have been doing this for about two months, so they are growing. I try to release at least a couple new ones every week. Click on Join. You will see a list of all the different membership options. Silver Members and up get access to all of the TechHelp extended cuts. But do not worry, I am going to keep releasing these free TechHelp videos too. As long as you keep watching them, I will keep making them. Make sure you like it. If you think someone you know will enjoy it, click on that share button. Do not forget to subscribe. If you want to get notifications whenever I release a new video, click on that little bell icon. Check down below, click on Show More, and you will see some links down there that will open up and show you other resources. I will put a link down there to my split database video. If you have not yet tried my free Access Level 1 course, it is three hours long, it is free, it is on YouTube, it is on my website, it covers all the basics. If you like that, Level 2 is just one dollar, and it is free for members. Want to see your question answered in a video like this? Visit my TechHelp page. That is all. Thanks for learning with Access Learning Zone, and we will see you next time. QuizQ1. What does the "Compact and Repair" feature in Microsoft Access do?A. It removes gaps and fixes problems in the database B. It deletes all records from your tables C. It increases the size of the database D. It exports the database to another format Q2. Why do gaps appear in an Access database table? A. Because Access reserves extra space for new records B. Because Access reuses deleted record space immediately C. Because deleted records leave empty spaces that are not reclaimed automatically D. Because Access compresses data automatically Q3. What is one benefit of compacting an Access database? A. It increases the file size for better performance B. It slows down the database on a network C. It removes wasted space caused by deleted records D. It requires fewer backups Q4. When should you compact your database more frequently? A. When only one person is using the database and there are few changes B. When many people are using the database and lots of adding and deleting occur C. When the database is rarely used D. When the database file never changes size Q5. What should you always do before running a Compact and Repair operation? A. Add new records to the database B. Make a manual backup of your database file C. Delete all existing data D. Disable all user accounts Q6. Why is it not recommended to compact an Access database over a network? A. It increases the risk of file corruption B. It is faster than compacting locally C. It automatically creates a backup D. It guarantees exclusive access Q7. What happens during the Compact and Repair process in Access? A. All objects are copied into a new database file B. The existing file is deleted without backup C. The database is converted to a different format D. User passwords are reset Q8. Where can you find the "Compact On Close" option in Access? A. Under Form Design Tools B. Under File, Options, and Current Database C. In the Ribbon's Insert tab D. In the Query Wizard Q9. Why might "Compact On Close" not be a good idea for large databases? A. It does not actually compact anything B. It slows down the closing process significantly C. It deletes recent data automatically D. It runs faster over a network Q10. What is the recommended method for compacting large databases used by many people? A. Compact directly on the network B. Have everyone stay logged in C. Copy the file to a local workstation, compact it, and copy it back D. Run the compact process simultaneously on multiple computers Q11. What is one advantage of splitting your database into front end and back end files? A. It allows bypassing the two gigabyte file size limit B. It disables user-level security C. It makes queries slower D. It reduces the need for table indexing Q12. In modern versions of Access, where does most wasted space in the database come from? A. Storing large text fields inefficiently B. Deletions of records C. Indexing every field D. Storing images Q13. Why is regular backup important when compacting and repairing Access databases? A. Because the process is risk-free B. Because Compact and Repair can corrupt the file in rare cases C. Because backups are only needed yearly D. Because Access makes automatic backups Q14. Which of the following is TRUE about Access efficiency in storing data in modern versions? A. It wastes a lot of space when using short text fields B. It is typically 98 to 99 percent efficient, especially after compacting C. It is less efficient the bigger the database becomes D. It compresses all data automatically on save Q15. What feature can you use to programmatically compact and repair a database in Access? A. A special macro only accessible to admins B. A single line of VBA code C. The print preview function D. An Excel add-in Answers: 1-A; 2-C; 3-C; 4-B; 5-B; 6-A; 7-A; 8-B; 9-B; 10-C; 11-A; 12-B; 13-B; 14-B; 15-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. SummaryToday's video from Access Learning Zone focuses on the importance of compacting and repairing your Microsoft Access database, along with the steps you should follow to keep your files running efficiently.The topic came up from a viewer's question about why a database with only a few thousand records could be approaching a gigabyte in size and what could be done to reduce it. This is a common issue many Access users face as their databases collect and then discard records over time. Access has a feature called Compact and Repair. The "repair" portion helps fix certain database problems, but the real benefit for file size comes from the "compact" function. As you add and then delete records, Access leaves behind gaps in your tables rather than immediately reclaiming that space. When you insert a new record, it typically gets added to the end instead of filling those holes. Over time, if you add a large number of records, delete some, and then add more, you can end up with a lot of wasted space. For example, if you add 200 megabytes of data, delete 100 megabytes, and then add another 100 megabytes, you could be left with a 300 megabyte file when the actual data only amounts to 200 megabytes. Compacting your database cleans up these gaps and compresses the file, making it smaller and running more efficiently. A compact database is also faster to open and navigate, especially if you are using it across a network. Access can also accumulate unnecessary data like temporary objects and old indexes. These are not always removed by default, so compacting the database regularly clears them out. The repair aspect of Compact and Repair can often fix small issues before they grow and potentially cause data corruption, particularly in long text fields. Newer versions of Access are much better about storage management, but in the past, improperly-sized fields would result in lots of wasted space. These days, Access optimizes text field storage much more efficiently. As for frequency, it really depends on your usage. If I am the only person working in my database, I might compact it once per week. For a larger environment where several people are adding and deleting records throughout the day, you may need to compact and repair more often. Watch your file sizes, determine how quickly they grow, and find a schedule that suits your needs. Once a week or even once a month may be enough for many newer databases. Before you compact and repair, always create a manual backup of your database. Even if you have automated backups, make a separate backup just before running this process, as there are rare occasions where compacting can corrupt your file. Have daily, weekly, and monthly backups so you can recover from any point in time if necessary. Make sure you have exclusive access to the database while performing a compact and repair. Nobody else should be logged in, particularly in a networked environment. The process is simple: open your database, go to the Database Tools tab, and select Compact and Repair. If you need to bypass any startup forms, hold down Shift while opening the file. When you run Compact and Repair, Access creates a new file from your existing database, deletes the old one, and renames the new file for you. There is a Compact On Close option in Access, which you can find in the options under the Current Database settings. This will automatically compact and repair your database every time you close it. I do not personally use this setting, especially for large files, since the process can take quite a while and can slow things down at close. For small databases, it is fine, but for anything larger than a few megabytes, it is better to run compact and repair manually. I also recommend that you avoid compacting your database over a network connection. Instead, copy the file down to your local machine, run the compact and repair process there, and then copy it back to the server. This reduces the risk of corruption. For those with larger databases or split databases, I often suggest considering splitting your database into more than one backend file if you are approaching the two gigabyte limit. You can put different big tables into separate backend files, which helps you work around file size limitations. Bear in mind that with multiple backend files, you lose some features such as referential integrity, but generally, separate smaller files are easier to manage and run more efficiently. Regarding actual file sizes and efficiency, I conducted my own experiments with record sizes and numbers and found that Access is exceptionally efficient with storage now – 98 to 99 percent efficient in most cases, even before compacting. The real inefficiency comes from the accumulation of deleted records. In today's extended cut video for members, I cover in detail the entire testing process I used to analyze data storage efficiency. I walk through building two databases, one to generate records of different lengths and another to control and monitor the results. I cover advanced techniques like controlling another Access database from VBA, using a wait function to monitor processes, compacting databases with code, reading and writing text files for input/output, deleting and renaming database files, and using recordsets to add data. If you want to learn more about splitting your Access database or seeing the full details behind my storage tests, I have lessons available on those topics as well. I will include links on my website. Do not forget to check out my free Access Level 1 course, which is about three hours long and available both on YouTube and my website. It covers all the basics you need to get started. Access Level 2 is available for just one dollar and is free to members. I always enjoy answering viewer questions in my TechHelp series. If you would like to see your question featured, visit my TechHelp page. 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 ListImportance of compacting and repairing databasesHow data deletions create gaps and waste space Impact of file size on database performance Removing junk files and database bloat Reducing database corruption risk with repairs How often to compact and repair your database Backing up databases before compact and repair Exclusive access requirements for compacting Steps to perform a compact and repair in Access Using the Compact On Close option Risks of compacting over a network Recommended method for compacting over a network Splitting databases to avoid size limits Effectiveness of compact and repair in new Access versions Test results on wasted space due to deleted records |
||||
|
| |||
| Keywords: TechHelp Access compact repair PermaLink Compact & Repair in Microsoft Access |
