Compacting Databases

With use, the internal structure of a database can become fragmented, leading to wasted disk space — and to poor performance if the fragmentation is excessive.

Compacting a database:

• Reorganizes a table’s pages so they reside in adjacent database pages. This improves performance because the table is no longer fragmented across the database.
  
  
• Reclaims unused space created by object and record deletions. When objects or records are deleted from the database, the space they occupied is marked as available for new additions to the database. However, the size of the database never shrinks unless the database is compacted. For databases in which objects and records are frequently added, deleted, and updated, you should compact frequently.
  
  
• Resets incrementing AutoNumber fields so the next value allocated will be one more than the highest value in the remaining records. For example, if all records in the database have been deleted, after compacting the database the value in the AutoNumber field will be 1 when the next record is added. If the highest remaining AutoNumber value in the database is 50, after compacting the value will be 51 when the next record is added. Note that this is true even if records containing values higher than 50 were added previously but were deleted prior to compacting.
  
  
• Regenerates the table statistics used in the query optimization process. These statistics can become out-of-date over time, typically if transactions were rolled back, or if the database was not properly closed due to power loss or failure to completely exit the program using Microsoft Jet before turning the computer off.
  
  
• Flags all queries so that they will be recompiled the next time the query is executed. This is important because database statistics can change and a previously compiled query may have an inaccurate query plan.

Before you can compact a database, the following conditions must be met:

• The user compacting the database must be logged on using an account that has Modify Design or Administer security permissions for all tables in the database. For more information on Microsoft Jet security, see Chapter 10, “Managing Security.”
  
  
• Sufficient disk space must exist for both the original and compacted versions of the database, even if the database is being compacted through the Microsoft Access user interface using the same file name. The compacted database is renamed as the original database only when compacting is successful.
  
  
• Other users must not have the database open. When a database is compacted, it has to be open exclusively by Microsoft Jet to prevent any users from accessing and modifying the database.

Note that you cannot compact the database that is currently open in Microsoft Access. You must close the database and compact it either from the Microsoft Access user interface or from another Visual Basic project.

Determining whether the current database is open exclusively is not simply a matter of trying to open the database. If you have the database open exclusively and try to open it again, you won’t receive a run-time error as you may expect. Actually, you can open a database as many times as you want to, exclusively or not, with the same DBEngine object.

To determine whether the current database is open exclusively, you can try to open the database using the PrivDBEngine object. For more information, see “Determining Whether the Current Database Is Open Exclusively” in Chapter 2, “Introducing Data Access Objects.”

You can compact a Microsoft Jet database using the CompactDatabase method of the DBEngine object. Although the primary use of this method is to compact a database, it supports a variety of other operations with the options argument. The syntax of the CompactDatabase method is:

DBEngine.CompactDatabase olddb, newdb [,locale [,options[,password]]]

In this syntax:

• olddb is the name of the database you want to compact.
  
  
• newdb is the name of the compacted database you want to create.
  
  
• locale is a string expression used to specify collating order (the language-specific sort order) for newdb.
  
  
• options is a constant or combination of constants that specify one or more options.
  
  
• password is a string expression that contains a password if the database is password-protected.

You can’t use the newdb argument to specify the same database file as the olddb argument.