Access Databases Compacting Tips

Compacting allows Access to rearrange the data in the same way you might rearrange your spice shelf. It fills in the space left behind by the deleted objects.

Compact an Access database periodically by selecting 'Compact and Repair Database' from Database Tools in the Microsoft Access Ribbon.

If you use auto number fields in your database (in versions prior to Access 2016), use ‘Tools’ under ‘Database Utilities’ to compact after you have viewed your subversion.

Select 'Help - About Microsoft Access' to check your subversion of Access. Your subversion must be up-to-date to avoid known flaws in Access. This defect causes auto number fields to pick previously picked numbers when adding new records.

  • Use Access 2000 current subversion is 9.0.4402 SP-1 (on computers running OSs other than Windows XP).
  • Use Access 2002’s running Windows XP subversion 10.4302.4219 SP-2 in order to compact your database, if you use auto number fields.
  • To compact your database on Windows XP computers, the minimum subversion is 9.0.6962 SP-3.
  • In version 2016 you can compact any database, regardless of the subversion.

When an Access database is compacted, table statistics are refreshed to optimize the stored queries. Never set the database to compact on close - there is no need to do this every time you close. Wiping out optimization information every time the program is closed means that the statistics are regenerated, and optimization must be redone as each query is edited or run. This optimization is time consuming and only needs to be done periodically or after a major upgrade.

Jet 4.0 Engine >

By Admin at 29 Nov 2016, 17:04 PM



Post a comment

Please correct the following: