Microsoft Access - Reclaim The Space Temporary Objects Use (Access 97/2000/2002/2003)


It's easy for Access to catch you by surprise if you aren't careful. Access 2000 and later have a 2 GB file size limit, which many people think they have no chance of ever approaching. (The limit in Access 97 is 1 GB.) However, even if your application's tables are relatively small, you can hit the size limit quicker than you might think if you perform tasks that heavily use temporary tables. Many people don't realize that Access doesn't automatically reclaim space when you delete records and objects in an Access database.


To ensure that your MDB files stay beneath the size at which problems will occur, compact them to reclaim the space that was allocated to deleted data and objects. To do so, choose Tools | Database Utilities | Compact And Repair Database from the menu bar (in Access 97, Tools | Database Utilities | Compact Database).


In Access 2000 and above, you can configure a database to automatically compact whenever it's closed. To do so, choose Tools | Options from the menu bar. Then, click on the General tab, select the Compact On Close check box, and click OK. If the database will be reduced by at least 256 kilobytes when you close it, Access automatically compacts and repairs the file. You need to activate this setting on a file-by-file basis.

Go back