Compacting a Database
As you add, delete and change records, modify forms, add macros and do any number of other tasks, Access does not reuse space used by deleted records. The file is not damaged in any way; it just has a lot of holes. So Access takes longer to read the tables and load objects. Every once in a while you should compact your database. Compacting removes these holes (like disk defragmenting does on your hard drive). It copies all of the objects and writes them "continguously" into a new database. How often you should compact a database depends on how much maintenance you have done. There have been times when I have worked on several forms in just a couple of hours and have seen the database double or triple in size. So I suggest you compact your database regularly. The Compact Databases archive contains three files to help you compact your databases. The text file is an explanation of the other two files. The COMPACT.BAT file makes a copy of your database into the C:\TEMP folder and then compacts the original database into a temporary database. The RENAME.BAT file then deletes the original database and renames the compacted database to the original name of the deleted database. You still have your original database in the C:\TEMP folder. These files were written to work with the Registration database found on this site. You can go in and modify these batch files to compact your databases. Use Notepad to modify these files. As an aside, the Registration database has procedures in it to count the number of times the database has been opened. After it has been open two times, a message appears when the user exits and suggests he/she compacts the database. Download Compact Databases archive Download Registration Databases To compact the database from within Access, open the database and click on Tools on the menu bar. Choose Database Utilities from the drop-down menu. Then click on Compact Database. The database will be compacted within itself. You should back up your databases before compacting them. To compact your database from the command prompt, type in C:\OFFICE\MSACCESS.EXE C:\PATH&FILENAME /COMPACT Include the complete path to the Office folder and all file extensions. |