As you and the users of your application work with a database, the database grows in size. To maintain a high state of performance, Access defers the removal of discarded pages from the database until you explicitly compact the database file. This means that as you add data and other objects to the database and remove data and objects from the database, Access does not reclaim the disk space that the deleted objects occupied. This not only results in a very large database file, it also ultimately degrades performance, as the physical file becomes fragmented on disk. Compacting a database accomplishes the following tasks:
Reclaims all space occupied by deleted data and database objects.
Reorganizes the database file so that the pages of each table in the database are contiguous. This improves performance because, as the user works with the table, the data in the table is located contiguously on the disk.
Resets counter fields so that the next value will be one more than the last
undeleted counter value. If, while testing, you add many records that you delete just prior to placing the application in production, compacting the database resets all the counter values back to 1.
Re-creates the table statistics used by the Jet Engine when it executes queries, and marks all queries so that Jet recompiles them the next time they are run. These are two very important related benefits of the compacting process. If you have added indexes to a table, or the volume of data in the table has changed dramatically, the query won't execute efficiently. This is because Jet bases the stored query plan it uses to execute the query on inaccurate information. When you compact the database, Jet updates all table statistics and the plan for each query to reflect the current state of the tables in the database.
TIP
It is a good idea to defragment the hard drive that a database is stored on before performing the compact process. The defragmentation process ensures that as much contiguous disk space as possible is available for the compacted database.
NOTE
In earlier versions of Access, the repair process was a separate utility from the compact process. With Access 2000, Access 2002, and Access 2003, there is no longer a separate repair process. The compact and repair processes both occur when you compact a database. When you open a database in need of repair, Access prompts you to compact it.
To compact a database, you can use one of five techniques:
Use commands provided in the user interface.
Click an icon you set up for the user.
Set up the database so that Access compacts it whenever you close it.
Use the CompactDatabase method of the JetEngine object.
Use the CompactRepair method of the Application object.
Regardless of which method you select for the compact procedure, the following conditions must be true:
The user performing the procedure must have the rights to open the database exclusively.
The user performing the procedure must have Modify Design permission for all tables in the database.
The database must be available for you or the user to open it for exclusive use. This means that no other users can be using the database.
The drive or network share that the database is located on cannot be read-only.
The file attribute of the database cannot be set to read-only.
Enough disk space must be available for both the original database and the compacted version of the database. This is true even if you compact the database to a database by the same name.
CAUTION
It is a good idea to back up the database before you attempt to compact it. It is possible for the compact process to damage the database. Also, do not use the compact process as a substitute for carefully following backup procedures. The compact process is not always successful. Nothing is as foolproof as a fastidiously executed routine backup process.
NOTE
If, at any time, Access detects that something has damaged a database, it will prompt you to repair the database. This occurs when you attempt to open, compact, encrypt, or decrypt the damaged database. At other times, Access might not detect the damage. Instead, you might suspect that damage has occurred because the database behaves unpredictably. This is when you should first back up and then perform the compact process, using one of the methods covered in this chapter.
Access provides a fairly straightforward user interface to the compact operation. To compact a currently open database, choose Tools, Database Utilities, Compact and Repair Database. Access closes the database, compacts it, and then reopens it.
To compact a database other than the currently open database, follow these steps:
Close the open database.
Choose Tools, Database Utilities, Compact and Repair Database. The Database to Compact From dialog box appears, as shown in Figure 30.1.
Select the database you want to compact and click Compact. The Compact Database Into dialog box appears, as shown in Figure 30.2.
Select the name for the compacted database. This can be the same name as the original database name, or it can be a new name (if you are compacting a database to the same name, make sure that it is backed up). Click Save.
If you select the same name, Access prompts you to replace the existing file. Click Yes.
To give users a very simple way to compact a database, you can create an icon that performs the compact process. You accomplish this using the /Compact command-line option, which compacts the database without ever opening it. The shortcut looks like this:
c:\MSOffice\Access\Msaccess.exe c:\Databases\TimeAndBilling.MDB /Compact
You can follow this syntax with a space and the name of a destination database if you do not want Access to overwrite the current database with the compacted version. If you do not include a path for the destination database, Access places it in the My Documents folder by default. You can have Access automatically create the shortcut for you using the Setup Wizard that ships with the Office 11 Developer. Chapter 32, "Distributing Your Application," covers this process.
To create a shortcut, follow these steps:
Open the folder where you have installed your application.
Right-click the application (MDB) icon for your database.
Choose Create Shortcut.
Right-click the shortcut you just created.
Choose Properties.
Click the Shortcut tab.
Modify the shortcut to appear with the syntax shown in the previous example.
Using the environmental setting Compact on Close, you can designate that Access will compact specific databases whenever the user closes them. Access compacts a database upon close only if it determines that the compact process will reduce the size by at least 256KB. To set the Compact on Close environmental setting:
Open the database that you want to affect. Select Tools, Options.
Click the General tab of the Options dialog box.
Click the Compact on Close check box.
NOTE
Although set in Tools, Options, the Compact on Close setting applies only to the database that is open when you select the option. This allows you to selectively designate which databases Access compacts when the user closes them.
CAUTION
Remember that when you use the Compact on Close option, the database must meet all the conditions ordinarily required for Access to compact a database. For example, if other users are in the database when someone tries to close it, the user trying to close the database receives an error.
Using the CompactDatabase method, you can compact a database using code. The CompactDatabase method is performed on a member of the Microsoft Jet and Replication Objects (JRO), the JetEngine object. It receives a source connection string and a destination connection string as parameters. Jet uses these connection strings to designate the source and destination databases, respectively. Jet also uses the Source Connection and Destination Connection parameters for the following purposes:
To change the locale of the database
To encrypt or decrypt the database
To convert the database from an older Jet version to a new version
To specify the user ID and password
The Locale Identifier property of the Destination Connection parameter determines the collating order in which Jet sorts the data in the compacted database. You will use this option when you are working with a database in which the data is stored in another language, and you want the data to be collated in a particular language.
The Jet OLEDB:Encrypt Database property of the Destination Connection parameter specifies whether you want the compacted database to be encrypted. If you do not specify this property, the compacted database will have the same encryption status as the original source database.
The Jet OLEDB:Engine Type property of the Source Connection parameter designates the version of the source database to open. The Jet OLEDB:Engine Type property of the Destination Connection parameter indicates the version of the new database. If omitted, the version of the source and destination databases is the same.
Finally, the User ID and Password properties of the Source Connection parameter enable you to supply the name of the user and the user's password for a database that is password protected.
The following code, contained in the basCompactDB module of Chap30Ex.MDB, compacts and encrypts a database called Chap30Big.MDB:
Sub CompactDB() Dim je As New JRO.jetengine Dim strFilePath As String 'Store path of current database in a variable strFilePath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")) 'If destination database exists, delete it If Len(Dir(strFilePath & "Chap30Small.mdb")) Then Kill strFilePath & "Chap30Small.mdb" End If 'Use the CompactDatabase method of the JetEngine 'object to compact the database je.CompactDatabase SourceConnection:= _ "Data Source=" & strFilePath & "Chap30Big.mdb", _ DestConnection:="Data Source=" & strFilePath & "Chap30Small.mdb; " & _ "Jet OLEDB:Encrypt Database=True" End Sub
The code names the compacted database Chap30Small.MDB. The code also encrypts the database during the compacting process.
For this code to execute successfully, remember that you must close the Chap30Big database, and the user running the code must have the right to open the database exclusively. Furthermore, the user must have Modify Design permissions for all tables in the database. Finally, because the JRO JetEngine object performs the CompactDatabase method, you must include a reference to the Microsoft JRO 2.1 Library. Access does not reference this library by default when you create a new Access database. You must use Tools, References to reference it.
An alternative to the JetEngine object is a method introduced with the Access 10 Application object. The CompactRepair method simplifies the process shown in the previous section:
Sub CompactDBApp() Dim strFilePath As String 'Store path of current database in a variable strFilePath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")) 'If destination database exists, delete it If Len(Dir(strFilePath & "Chap30Small.mdb")) Then Kill strFilePath & "Chap30Small.mdb" End If 'Use the CompactRepair method of the application object 'to compact and repair the database Application.CompactRepair strFilePath & "Chap30Big.mdb", _ strFilePath & "Chap30Small.mdb", True End Sub
The code, located in basMaintenance, declares a string variable. The Left and InstrRev functions extract the current path from the Name property of the CurrentDB object. If the designation file is located in the current folder, the code deletes it. The CompactRepair method of the Application object compacts and repairs the database into the designated destination database. The CompactRepair method receives three parameters. The first is the name and location of the source database, the second is the name and location of the destination database, and the third is whether you want Jet to log the operation.