ACC: Defragment and Compact Database to Improve Performance

This article was previously published under Q92681
This article has been archived. It is offered "as is" and will no longer be updated.
Novice: Requires knowledge of the user interface on single-user computers.

You can improve the performance of Microsoft Access if you periodicallydefragment your hard disk and compact your database.
Because the data on your hard disk becomes fragmented over time, youshould run a disk-defragmentation utility (or defragmenter)periodically. If you often make changes to a database, portions of thedatabase may also become fragmented. Therefore, you should run the CompactDatabase command in Microsoft Access periodically.

A disk defragmenter will place the database file in contiguous clusters onyour hard disk, making file access faster. If you do not defragment yourhard disk, the operating system may have to go to several physicallocations on the disk to retrieve the database file, making file accessslower.

Running the Compact Database command may also improve the performance ofMicrosoft Access. Compact Database makes a copy of the database file and,if it is fragmented, rearranges how the database file is stored on disk.The compacted database file is usually smaller than the original.Compacting can also speed up queries because it writes all the data in atable into contiguous pages on the hard disk. Scanning sequential pages ismuch faster than scanning fragmented pages.

You can use the original name for the compacted database file, or you canuse a different name to create a separate file. If you use the same nameand the database is compacted successfully, Microsoft Access automaticallyreplaces the original file with the compacted version.

Limitations of Compacting

  • For the compact operation to succeed, you must have enough storage space on your hard disk for both the original and the compacted database.
  • You cannot compact an open database. In a multiuser environment, the compact operation fails if another user has the database open.

    NOTE: In Microsoft Access 97, you can compact a database while it is open, as long as the database has been opened exclusively.

Defragment or Compact First?

If you compact a database after running a defragmenter, you theoreticallyleave open disk space immediately after the .MDB file on the disk,allowing the operating system to place any additional information in thesucceeding physical clusters. This would be very fast. However, if youdefragment after running Compact Database, your .MDB may be placed on thefirst part of the disk followed by the rest of your files, with no opendisk space until the end (the inside tracks) of the disk. This makes diskaccess somewhat slower.
For more information about compacting databases, type "compactingdatabases" in the Office Assistant, click Search, and then click to view"Compact a database to defragment the file and free disk space."

Article ID: 92681 - Last Review: 10/14/2013 17:58:29 - Revision: 2.1

Microsoft Access 1.0 Standard Edition, Microsoft Access 1.1 Standard Edition, Microsoft Access 2.0 Standard Edition, Microsoft Access 95 Standard Edition, Microsoft Access 97 Standard Edition

  • kbnosurvey kbarchive kbinfo kbusage KB92681