| home Java Script MS Access Perl HTML Delphi C ++ Visual Basic Java CGIPerl MS Excel Front Page 98 Windows 98 Ms Word Builder PHP Assembler Link to us Links |
Although there isn't too much that you need to do to maintain an Access database, you must know a couple of important techniques in order to ensure that your databases are maintained as effectively as possible. The two techniques that you must be familiar with are compacting and repairing. In short, compacting a database means removing unused space, and repairing a database involves repairing a damaged database (.MDB file). Both of these processes and the various ways that you can accomplish them 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 results not only in a very large database (.MDB) file, but also it ultimately means a degradation in performance as the physical file becomes fragmented on disk. Compacting a database accomplishes the following tasks:
It is a good idea to defragment a hard disk before performing the compact process. This ensures that as much contiguous disk space as possible is available for the compacted database.
The following three methods exist for compacting a database:
Regardless of the method that you select for the compact procedure, the following conditions must be true:
The Access user interface provides the user with a fairly straightforward interface to the compact operation. To compact a database using the user interface, the following steps must be performed:
Figure 33.1. The Database To Compact From dialog.
Figure 33.2. The Compact Database Into dialog.
A very simple way to provide the user a means by which to compact a database is to create an icon that performs the compact process. This is accomplished using the /Compact command-line option. The /Compact command-line option compacts the database without ever opening it. The shortcut looks like this:
c:\MSOffice\Access\Msaccess.exe c:\Databases\TimeAndBilling.MDB /Compact
The shortcut can be automatically created for you using the setup wizard that ships with the Access Developer's Toolkit. This is covered in Chapter 34, "Distributing Your Application." To create a shortcut yourself, follow these steps:
Using the CompactDatabase method, you can compact a database using code. The CompactDatabase method is performed on the DBEngine object. It receives the old database and new database as parameters. In addition, it receives the following optional parameters:
The locale is an optional string determining 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 collated in a particular language.
The Options argument is used to specify whether you want the compacted database to be encrypted as well as what version you want the database to be compacted into. The two constants that can be used for encryption are dbEncrypt and dbDecrypt. If you do not specify either of these constants, the compacted database will have the same encryption status as the original source database. An additional constant can be specified within the Options argument. This constant determines the version of the data within the compacted database. The available options are dbVersion10, dbVersion11, dbVersion20, and dbVersion30. The CompactDatabase method converts only data, not the objects within the database.
Finally, the Password argument enables you to supply the password for a database that is password protected.
The following code, contained within the basUtils module of Chap33Ex.MDB, compacts and encrypts a database called Chap33Big.MDB. It uses the dbLangGeneral locale, which is appropriate for English, German, French, Portuguese, Italian, and Modern Spanish. The compacted database will be called Chap33ExSmall.MDB.
Sub CompactDB()
DBEngine.CompactDatabase "c:\databases\Chap33Big.MDB", _
"c:\databases\Chap33Small.MDB", _
dbLangGeneral, dbEncrypt
End Sub
For this code to execute successfully, remember that the Chap33Big 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 or Administer permissions for all tables within the database.
The repair process is used when a database has been damaged. Damage can occur to a database when the power is interrupted while the database is open or for several other reasons. Regardless of the cause of the damage, a damaged database can often be salvaged using the repair utility. As with the compact process, the repair process can be executed from within the Access interface, using a desktop shortcut or using DAO code. In order to perform the repair process, the follow conditions must be met:
It is a good idea to back up the database before attempting to repair it. It is possible for the repair process to do further damage to the database. Also, do not use the repair process as a substitute for carefully following backup procedures. The repair process is not always successful. Nothing is as fool-proof as a fastidiously executed backup process.
As with the compact process, the Access interface provides a fairly straightforward interface to the repair operation. The following steps enable you to repair a database using the user interface:
Figure 33.3. The Repair Database dialog.
Figure 33.4. This message appears when the repair process is successful.
Just as the /Compact command-line switch can be used to compact a database, the /Repair command-line switch can be used to repair a database. The format is as follows:
c:\MSOffice\Access\Msaccess.exe c:\Databases\TimeAndBilling.MDB /Repair
The shortcut can be created using exactly the same method as is covered in the "Providing the User with a Shortcut to the Compact Process" section of this chapter.
When a database is repaired, it might increase in size. This is due to indexes that are created to assist with the repair process. It is therefore a good idea to compact a database after you repair it. This can be accomplished using the combination of the /Repair and /Compact switches. When you specify both command-line switches, Access always repairs the database first, before compacting it.
Just as you can compact a database using code, you can also repair it using code. The following subroutine illustrates this process:
Sub RepairDB()
Dim db As DATABASE
On Error Resume Next
Set db = OpenDatabase("c:\databases\Chap33Damaged.MDB")
If DBEngine.Errors.Count > 0 Then
If Err = 1000 Then 'Database Corrupt
MsgBox "Database is Corrupt..Attempting to Repair"
DBEngine.RepairDatabase "c:\databases\Chap33Damaged.MDB"
End If
End If
End Sub
The RepairDB subroutine attempts to open the Chap33Damaged database. If an error occurs on the OpenDatabase method, the On Error Resume Next statement causes the next line of code to execute. The next line of code evaluates to see whether the error was number 1000, indicating that the database is corrupt. If so, the RepairDatabase method is performed on the DBEngine object using the name of the damaged database as an argument.
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. Fortunately, Access provides you with three simple methods of completing the compact process. This chapter covered all three methodologies in detail.
Hopefully, you will never have to repair a database. If a database is damaged, it might be repairable. This chapter covered issues surrounding the process of repairing a database as well, as the three methods that you can use to repair any Access database. The important thing to remember is that the repair utility is in no way a substitute for proper backup techniques.