Compacting Databases

Compacting is good to use as a backup mechanism because it ensures that you have full ownership of the file when you do the transfer. Before I explain this backup technique, I will first describe how to compact a database, as using the compact option regularly on your Access database is an important administrative task. To compact a database, first open the database that you want to use and then choose Tools ˜ Database Utilities ˜ Compact and Repair. For Access 97, where the compact and repair functions are separated, you should regularly run the Repair option as well because doing so may resolve an as-yet unreported problem in the database.

Note  

If you delete or modify tables or objects, your database will fragment. Fragmenting leads to a drop in performance and can be associated with database issues. Compacting from within a database makes a copy of the database, rearranging how the database file is stored on disk. Once that file is compacted , the new file replaces the existing database.

In installations where the DBA is actually an end user , I find that no matter how many times I ask the reluctant DBA, he or she forgets about the compacting option. To help resolve this issue, I will usually add a form to the database that will make compacting easier. I have called this form frmCompactEasy in the demonstration databases. It works by using the SendKeys method to send the keystrokes that drive the Compact Database menu option.

 ' Use the SendKeys command to compact the database safely.   SendKeys "%TDC" 

Compacting makes your database faster and more stable, and the following section explains how to use compacting to back up your database.

Using Compacting to Back up Your Database

If you have used Access 2 or Access 95, you will remember that compacting databases was actually a three-step process. First, you had to use the compact option to make a new compacted database. Then you had to move the existing file to another folder, and finally you had to rename the compacted database back to the same name as the existing database. Naturally, compacting was not so popular in those days.

Even though the old method isn't used for compacting much these days, it can come in handy for doing backups . To use it, we will back up the database by compacting from the current database to a new one in a different location. To find this option, you will need to start Access without selecting any database and then choosing Tools ˜ Database Utilities ˜ Compact and Repair Database, as shown in Figure 5-4.

Figure 5-4: Choosing to compact a database for backup purposes.

You will then need to select the database (by using the File dialog) that you want to compact. After that, enter the name and location of the database that you want to backup and compact into (as shown in Figure 5-5). I suggest that you always give your backups a name that denotes it as a backup, and you should store it in a different folder so that the databases are not mixed up. I also advise that you save your backups in a folder that is difficult to find or protected from users by operating system permissions.

Figure 5-5: Selecting a new folder and entering a name that reflects that the database is a backup.

In Access 2002, the Application object gained a new method that made it easier to back up from one database to another. The next section demonstrates this method.

Compacting and Repairing a Database for Backups by using VBA Code

Access 2002 introduced a new VBA method ”the CompactRepair method ”that allows you to compact from one database to another. I've adopted this method as part of a function that will run the compacting backup process for you. You will find this code in the sample database in a form called frmCompactDatabase . When using this code, you will need to change the values of the constants to suit your own database and backup naming preferences.

 Private Sub cmdCompactAnother_Click() Dim compactOK As Boolean, backupName As String Const COMPACTFROM = _  "C:\program files\Microsoft Office\Office 10\Samples\northwind.mdb" Const COMPACTTO = "C:\temp\northwind.mdb" compactOK = CompactToNewMDB(COMPACTFROM, COMPACTTO, True) If compactOK Then   MsgBox "A newly compacted database called " & COMPACTTO & " has been created." Else   MsgBox "Compact was unsuccessful." End If End Sub 

Caution  

The CompactToNewMDB function, described following, deletes an existing file if you set the deleteMdbToFirst argument to True. You should experiment with this function on an unimportant database before implementing it for your backup procedures.

The CompactToNewMDB function deletes the database before compacting is undertaken. If the database already exists in the location to which you want to compact, the Access CompaqRepair method will fail. For this reason, I have incorporated an optional deleteMdbToFirst argument to allow you to delete the destination database first. To run this process to generate multiple backups of your database, I suggest that you incorporate the date and time into the new database file name to make it unique.

 Function CompactToNewMDB(mdbFrom As String, mdbTo As String, _  Optional deleteMdbToFirst As Boolean) As Boolean  ' Input values: the paths and file names of the source and destination files  ' plus a Boolean flag that will delete your database if set to True. On Error GoTo CompactToNewMDB_Error   If IsMissing(deleteMdbToFirst) Then     deleteMdbToFirst = False   End If   If deleteMdbToFirst Then     If Len(Dir(mdbTo)) > 0 Then       ' Delete the database that you are compacting to.       Kill mdbTo     End If   End If     ' Compact and repair the database. Use the return value of     ' the CompactRepair method to determine if the file was     ' successfully compacted.   CompactToNewMDB = Application.CompactRepair(mdbFrom, mdbTo) CompactToNewMDB_Exit:   Exit Function CompactToNewMDB_Error:   ' You can add your own error handling code here.   Select Case Err.Number   Case Else     MsgBox "Error number " & Err.Number & " -> " & Err.Description   End Select   Resume CompactToNewMDB_Exit End Function 

If you are using Access 97 or 2000, you will find examples of how to compact from one database to another in the VBA help. Search for the term "CompactDatabase" in the Visual Basic Editor.

In the remainder of the chapter, we will concern ourselves with backing up tables and objects from the database into text format.