Compact an Access database periodically by selecting 'Compact and Repair'. 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.
Use 'Tools'... to select compacting from 'Database Utilities'... but only after you have viewed your subversion, if you use auto number fields in your database.
Select 'Help'... 'About Microsoft Access' to check your subversion of Access. Your subversion must be up to date in order to avoid a known flaw 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). Access 2002 running Windows XP subversion is 10.4302.4219 SP-2 in order to compact your database, if you use auto number fields.
Compact your database on Windows XP computers with the minimum sub version is 9.0.6962 SP-3.
Never set the database to compact on close. When an Access database is compacted, table statistics are refreshed in order to optimize the stored queries. There is no need to do this every time you close. Wiping out optimization information every time you close 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.
If you haven't time to implement these tips or if the development resources available to you are unable to make progress, please contact us.
Jet 4.0 Engine >