Chapter 30. Maintaining Your Application

IN THIS CHAPTER

• Why This Chapter Is Important
• Compacting Your Database
• Backing Up Your Database
• Converting an Access Database
• Detecting Broken References
• Practical Examples: Maintaining Your Application

Why This Chapter Is Important

Although you don’t need to do too much to maintain an Access database, you must know about a few important techniques that you should use to ensure that you maintain your databases as effectively as possible. The first technique is compacting. Compacting a database means removing unused space from a database (.accdb or .mdb file). The second technique involves backing up your databases. Without a proper backup procedure in place, you are like a circus performer without a safety net. Another useful technique to have at your disposal is the capability to convert a database created in an earlier version of Access to the .accdb file format. Finally, it is important that you are able to detect broken references within your database. This chapter covers the compacting process and the ways you can compact. It also covers all the other maintenance techniques available to you within Microsoft Access.

Compacting Your Database

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, but 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 Access Database Engine when it executes queries and marks all queries so that the Access Database Engine recompiles them the next time they are run. These are two 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. The reason is that the Access Database Engine bases the stored query plan it uses to execute the query on inaccurate information. When you compact the database, the Access Database Engine updates all table statistics and the plan for each query to reflect the current state of the tables in the database.

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 compacting 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.

Using the User Interface

Access provides a fairly straightforward user interface to the compacting operation. To compact a currently open database, click the Microsoft Office Access button and select Manager, 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:

1. Close the open database.
2. Click to select the Microsoft Office Access button and then choose Manage, Compact and Repair Database. The Database to Compact From dialog box appears, as shown in Figure 30.1.

   Figure 30.1. The Database to Compact From dialog box allows you to select the database you want to compact.

   

3. Select the database you want to compact and click Compact. The Compact Database Into dialog box appears, as shown in Figure 30.2.

   Figure 30.2. The Compact Database Into dialog box allows you to select the name for the compacted database.

   

4. 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.
5. If you select the same name, Access prompts you to replace the existing file. Click Yes.

Using a Shortcut

To give users a simple way to compact a database, you can create an icon that performs the compacting process. You accomplish this by using the /Compact command-line option, which compacts the database without ever opening it. The shortcut looks like this:

c:Progam FilesMicrosoft Office 2007Office12
Msaccess.exe c:DatabasesTimeAndBilling.ACCDB /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.

To create a shortcut, follow these steps:

1. Open the folder where you have installed your application.
2. Right-click the application (ACCDB) icon for your database.
3. Choose Create Shortcut.
4. Right-click the shortcut you just created.
5. Choose Properties.
6. Click the Shortcut tab.
7. Modify the shortcut to appear with the syntax shown in the previous example.

Compacting Whenever a Database Closes

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, follow these steps:

1. Open the database that you want to affect. Click the Microsoft Office Access button and select Access Options. The Access Options dialog box appears.
2. Click the Current Database tab of the Access Options dialog box (see Figure 30.3).
3. Click the Compact on Close check box.

Figure 30.3. Select the Current Database tab of the Access Options dialog box.

Using the CompactDatabase Method of the JetEngine Object

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. The Access Database Engine uses these connection strings to designate the source and destination databases, respectively. The Access Database Engine 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 the Access Database Engine sorts the data in the compacted database. You 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 this property is 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.ACCDB, compacts and encrypts a database called Chap30Big.ACCDB:

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.

Using the CompactRepair Method of the Application Object

An alternative to the JetEngine object is a method introduced with the Access 2007 Application object. The CompactRepair method simplifies the process shown in the preceding section:

The code, located in basCompactDB, 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.

Backing Up Your Database

Introduced with Access 2003 is the capability to back up your database from within Microsoft Access. Here’s the process:

1. Open the database that you want to back up.
2. Click the Microsoft Office Access button and select Manage, Back Up Database. The Save As dialog box appears (see Figure 30.4).

   Figure 30.4. The Save As dialog box allows you to provide the name and location for the backup database.

   

3. Supply a filename and location for the database that you are backing up.
4. Click Save. Access creates a backup with the name and location that you designated.

Because the backup process simply creates a copy of the open database in a name and location that you specify, restoring the database involves moving and/or renaming the backup database file to the production location and name. You can then simply open the backup database and continue working as usual.

Converting an Access Database

Access 2007 makes it easy to interact with other versions of Access. Access 2007 allows you to open, read, and update Access databases stored in the Access 2000 file format and the Access 2002–2003 file format, without converting the files to the Access 2007 file format. It is important to note that as long as the database is stored in the MDB file format, you will not be able to take full advantage of Microsoft Office Access 2007’s rich set of new features.

As mentioned earlier, you can use Access 2000 files and Access 2002–2003 files with Access 2007. If you want to convert an open database stored in the Access 2000 or Access 2002–2003 file format to the Access 2007 file format, take the following steps:

1. Click the Microsoft Office Access button and select Convert (see Figure 30.5). The Convert Database Into dialog box appears.

   Figure 30.5. The Convert Database option appears after you click the Microsoft Office Access button.

   

2. Select a location and filename and then click Save to complete the process. Access warns you that the database has been upgraded and cannot be shared with users of Access 2003 or earlier versions (see Figure 30.6).

Figure 30.6. Access warns you that you cannot use the ACCDB file with previous versions of Access.

A method introduced with Access 2002 makes it easy to programmatically convert an Access database from one version to another. The code, found in basMaintenance, looks like this:

To begin, the code declares a string variable. It uses the built-in Left and InStrRev functions 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, the code deletes it. The code uses the ConvertAccessProject method of the Application object to convert the Chap30Small.mdb database, located in the current folder and stored in the Access 2002–2003 file format, to the Chap30V2007.ACCDB database, located in the current folder and stored in the Access 2007 file format.

Detecting Broken References

Prior to Access 2002, it was difficult to locate and diagnose broken references. Access 2007 offers BrokenReference, a property of the Application object that rectifies this problem. If broken references exist, the property evaluates to True. If no broken references exist, it evaluates to False. Querying the BrokenReferences property is much more efficient than looping through each reference to determine whether it is intact. The code, found in basMaintenance, looks like this:

Practical Examples: Maintaining Your Application

Begin by using the techniques you learned to back up a database. Then practice compacting the database using each of the five methods covered in the chapter. Finally, determine whether your database has any broken references.

Summary

You should perform the compacting process regularly—especially on databases containing your application data. The compacting 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 you should compact it. Although you should consider the compacting process an important part of the database maintenance process, remember that there is absolutely no substitute for proper backup techniques. This chapter also showed you a feature included in Access 2007 that allows you to back up an open database.

In addition to compacting your database, you need to understand the database conversion options available to you. You can convert databases from one version of Access 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 BrokenReference property makes this process much easier, by reporting whether all database references are intact. Using all the techniques covered in this chapter should save you a lot of time and effort in maintaining and working with your databases.