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:

To compact a database, you can use one of five techniques:

Regardless of which method you select for the compact procedure, the following conditions must be true:

c:MSOfficeAccessMsaccess.e XE c:DatabasesTimeAndBilling.MDB /Compact

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

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

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.