Although you don’t need to do too much to maintain an Access database, you must know about an important technique to ensure that your databases are maintained as effectively as possible. The technique, which you should be familiar with, is compacting. Compacting a database means removing unused space from a database (MDB file). The compact process and the ways you can accomplish it are covered in this chapter.
As you and the users of your application work with a database, the database grows in size. In order 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, the disk space that was occupied by the deleted objects is not reclaimed. This not only results in a very large database (MDB) file, but it also ultimately degrades performance as the physical file becomes fragmented on disk. Compacting a database accomplishes these 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 queries are executed and marks all queries to be recompiled the next time they are run. These are two very important related benefits of the compacting process. If indexes have been added to a table or the volume of data in the table has been dramatically changed, the query won’t execute efficiently. This is because the stored query plan Jet uses to execute the query is based on inaccurate information. When the database is compacted, all table statistics and the plan for each query are updated to reflect the current state of the tables in the database.
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.
In earlier versions of Access, the repair process was a separate utility from the compact process. With Access 2000 and Access 2002, there is no longer a separate repair process. The compact and repair processes both occur when a database is compacted. When you open a database in need of repair, you are prompted 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 it is compacted 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 to be opened 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 the database is compacted to a database by the same name.
It is a good idea to back up the database before attempting 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.
If, at any time, Access detects that a database is damaged, you will be prompted 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. The database then is closed, compacted, and reopened.
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, you are prompted 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. This is accomplished using the /Compact
command-line option, which compacts the database without ever opening it. The shortcut looks like this:
c:MSOfficeAccessMsaccess.e XE c:DatabasesTimeAndBilling.MDB /Compact
This syntax can be followed by a space and the name of a destination database if you do not want the current database to be overwritten by the compacted version. If you do not include a path for the destination database, it is placed in the My Documents
folder by default. The shortcut can be created automatically for you using the Setup Wizard that ships with the Office 2002 Developer. This is covered in Chapter 32, “Distributing Your Application.”
To create a shortcut, follow these steps:
Using the environmental setting Compact on Close
, you can designate that specific databases should be compacted whenever they are closed. A database is compacted upon close only if Access determines that the size will be reduced 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.
Click the Compact on Close
check box.
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. These connection strings are used to designate the source and destination databases, respectively. The Source Connection
and Destination Connection
parameters are also used 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 the data in the compacted database will be sorted. This option is used 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 compacted database is called Chap30Small.MDB. During the compact process, the database is also encrypted.
In order for this code to execute successfully, remember that the Chap30Big database must be closed, 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 CompactDatabase
method is performed on the JRO JetEngine object, you must include a reference to the Microsoft Jet and Replication Objects 2.1 Library. This library is not referenced 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 new to the Access 2002 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, it is deleted. 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 the operation to be logged.
Access 2002 makes it much easier to interact with other versions of Access. Access 2002 allows you to open, read, and update Access databases stored in the Access 2000 file format, without converting the files to the Access 2002 file format! Furthermore, Access 2002 allows you to easily convert files stored in the Access 2002 file format to either the Access 97 or the Access 2000 file format.
To convert an Access 2002 database to a format compatible with an earlier version of Access, select Tools|Database Utilities|Convert Database|To Access 2000 File Format or Tools|Database Utilities|Convert Database|To Access 97 File Format.
As mentioned earlier, Access 2000 files are fully operational in Access 2002. If you wish to convert an open database stored in the Access 2000 file format to the Access 2002 file format, select Tools|Database Utilities|Convert Database|To Access 2002 File Format.
Prior to Access 2002, when problems occurred during the conversion process, users were left wondering exactly what had gone awry. Access 2002 addresses this problem. If errors occur while converting from earlier versions of Access to the Access 2002 file format, a table is created listing each error. You can easily use the data in this table to handle the conversion problem gracefully.
A new Access 2002 method makes it easy to programmatically convert an Access database from one version to another. The code looks like this:
Sub ConvertAccessDatabase() Dim strFilePath As String 'Store current file path into variable strFilePath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "")) 'Delete destination database if it exists If Len(Dir(strFilePath & "Chap30V97.mdb")) Then Kill strFilePath & "Chap30V97.mdb" End If 'Convert source database to Access 97 file format Application.ConvertAccessProject strFilePath & "Chap30Big.mdb", _ strFilePath & "Chap30V97.mdb", _ DestinationFileFormat:=acFileFormatAccess97 End Sub
To begin, the code declares a string variable. The built-in Left
and InStrRev
functions are used to extract the path associated with the current database and place it in the strFilePath
variable. If the destination database exists in the current folder, it is deleted. The ConvertAccessProject
method of the Application
object is used to convert the Chap30Big.mdb
database, located in the current folder and stored in the Access 2002 file format, to the Chap30V97.MDB
database, located in the current folder and stored in the Access 97 file format.
Prior to Access 2002, it was difficult to locate and diagnose broken references. Access 2002 offers BrokenReference, a new property of the Application object that rectifies this problem. If broken references are found, the property evaluates to True
. If no broken references are found, it evaluates to False
. Querying the BrokenReference property is much more efficient than looping through each reference to determine if it is intact. The code looks like this:
Sub DetectBrokenReference() 'Display whether or not database contains a broken reference MsgBox Application.BrokenReference End Sub
The compact process should be performed regularly—especially on databases containing your application data. The compact process provides major benefits in terms of both performance and conservation of disk space. The more activity that occurs on a database, the more frequently it should be compacted. Although the compact process should be considered an important part of the database maintenance process, remember that there is absolutely no substitute for proper backup techniques.
In addition to compacting your database, it is important to understand the database conversion options available to you. Databases can be converted from one version to another using either the user interface or code. Finally, whereas it was an arduous, time-consuming process to detect broken references prior to Access 2002, the new BrokenReference property makes this process much easier, by reporting whether or not all database references are intact. Using all of the techniques covered in this chapter should save you a lot of time and effort in maintaining and working with your databases.
3.129.24.180