Periodically, you should compact the Access database. Compacting allows Access to rearrange the data by filling in the space left behind by the deleted objects. To do this, choose ‘Tools’… ‘Database Utilities’… Compact and Repair.
Warning! Make sure your subversion of Access is up to date otherwise you may encounter a bug in Access that causes auto number fields to pick previously picked numbers when adding new records. Access 2000 current subversion is 9.0.4402 SP-1 (on computers running OSs other than Windows XP). Access 2002 running Windows XP subversion is 10.4302.4219 SP-2. For Windows XP computers the current sub version is 9.0.6962 SP-3. To view the subversion, select ‘Help‘… ‘About Microsoft Access’.
Do NOT set the database to compact on close. Each time the database is compacted the table statistics are refreshed. The table statistics are used to optimize the stored queries, however the optimization information is wiped out when the statistics are regenerated and the optimization is redone when each query is edited or run. This optimization process can take up to several seconds.
The world’s only Microsoft Access Enterprise Support Service Offering!
