Hack 12. Stop the Database from Growing
Access databases are notorious for their ability to grow in size. This is especially true as data is moved in and out. For example, when a database application regularly imports data, processes the data, and then exports it back out, the database can become huge, on the order of several megabytes in size. This can be the case even when the data moving in and out is of a reasonable size.
To shrink the database back to the size it should be, you need to compact the database. However, expecting users to compact their databases isn't a great idea, especially if your users aren't technically savvy. Luckily, Access includes an option to compact a database when it is closed. This option was not available in older versions of Access, but it is available in Access 2002 and Access 2003.
Figure 1-26 shows the Options dialog box (Tools Options) with the General tab on top. Note the Compact on Close checkbox.
Access is unlike other products, such as SQL Server, in that it doesn't allow you to control the size of the database. Setting the database to compact each time it closes removes what has traditionally been a recurring problem with Access.
Figure 1-26. Selecting to compact on close