Database Maintenance

As your application is used, the database file can grow in size. If you are responsible for the maintenance of your database, you’ll need to perform a number of system-oriented tasks from time to time, such as compacting the database and repairing it if it becomes corrupted. This section discusses these maintenance tasks.

If you are performing compact and repair operations, you will often use the menu commands provided in the user interface. The Compact Database and Repair Database commands are both available on the Database Utilities submenu (Tools menu) in the Microsoft Access startup window.

However, if you want to provide a way for your users to compact and repair their copies of your application, you can provide them with either an icon that performs the compact and repair operations or code that compacts and repairs the database automatically.

You can create a shortcut that uses the /compact or /repair command-line options. You can specify either option, or both. If you specify both the /compact and /repair options, the repair always happens before the compact. These command-line options compact and repair your application without opening it. When you create the shortcut, type a command line that consists of:

The Microsoft Access application file name, MSAccess.exe. If you are using Microsoft Windows NT Workstation, you must specify a full path to the Microsoft Access application file.
The file name of the front-end database.
Either the /compact option, the /repair option, or both.

Important To compact or repair a database, the database and any connections to it must be closed. Also, if you have established user-level security for your application, the user running the repair or compact operation must have Open Exclusive permission for the database, and have Modify Design or Administer permission for all tables in the database. For more information on permissions, search the Help index for “setting permissions.” Additionally, when compacting, there must be sufficient disk space for both the original and compacted versions of the database, even if the database is being compacted to the same file name. This is because the compacted database is renamed as the original database only after compacting to a temporary file is successful.

Compacting a Database

To maintain a high state of performance, Microsoft Access defers the removal of discarded pages until you shut down the database and compact the discarded pages. This design keeps the interactive performance of your database high at the expense of recoverable disk space.

Compacting a database:

Reorganizes a table’s pages so they reside in adjacent database pages. This improves performance because the table is no longer fragmented across the database. If a table has a primary key defined, Microsoft Access copies the records in primary key order, which makes reading ahead more efficient. This produces an effect similar to clustered indexes in Microsoft SQL Server; however, unlike true clustered indexes, Microsoft Access doesn’t maintain primary key order when users add, delete, or modify records after the database is compacted. If a table doesn’t have a primary key, Microsoft Access copies records in the order that the records are stored on disk.
Reclaims unused space created by object and record deletions. When objects or rows are deleted from the database, the space they occupied is marked as available. However, the size of the database doesn’t shrink unless the database is compacted.
Resets incrementing AutoNumber fields (called Counter fields in Microsoft Access versions 2.0 and earlier) so the next value allocated will be one more than the last undeleted record.
Regenerates the table statistics used in the query optimization process. These statistics can become out-of-date over time, typically due to transactions that were rolled back or to the database not being closed properly.
Flags all queries so that they will be recompiled the next time the query is run. This is important because database statistics can change, which could cause a previously compiled query to contain inaccurate optimization information.

To compact your database, point to Database Utilities on the Tools menu and click Compact Database. If the database you want to compact is currently open, it will be closed, compacted into a temporary file, and then reopened. If no database is open, you must specify the database to compact and the database to compact into. If you specify the same file name to compact into, Microsoft Access compacts into a temporary file. When Microsoft Access compacts to a temporary file, it renames the temporary file back to the original file name once the compacting process is completed.

You can also use the CompactDatabase method of the DBEngine object to compact a database. When you use the CompactDatabase method, you must compact to a file with a different name. The following code example uses the CompactDatabase method to compact a database to a temporary file and then renames that temporary file to the original name if the compacting process is successful.

Sub CompactDB()
	On Error GoTo CompactDB_Err
	Const conFilePath = "C:\Program Files\Microsoft Office\Office\Samples\"

	' Compact the database to a temp file.
	DBEngine.CompactDatabase conFilePath & "Northwind.mdb", _		conFilePath & "NorthTemp.mdb"

	' Delete the previous backup file if it exists.
	If Dir (conFilePath & "Northwind.bak") <> "" Then
		Kill conFilePath & "Northwind.bak"
	End If

	' Rename the current database as backup and rename the temp file to	' the original file name.
	Name conFilePath & "Northwind.mdb" As conFilePath & "Northwind.bak"
	Name conFilePath & "NorthTemp.mdb" As conFilePath & "Northwind.mdb"
	MsgBox "Compacting is complete"

Exit_CompactDB:
	Exit Sub
	
CompactDB_Err:
	MsgBox Err.Description
	Resume Exit_CompactDB
End Sub

The CompactDatabase method also gives you the option of changing the encryption state, the version, and the disk or folder of a database while it’s being compacted. In other words, you can choose to convert an encrypted database to a non-encrypted database, or vice versa.

For example, the following code compacts a database named Old and creates a new, encrypted database named New.

DBEngine.CompactDatabase "C:\Old.mdb", "C:\New.mdb", _
	dbLangGeneral, dbEncrypt

See Also For more information on database encryption, see “Encrypting a Database” in Chapter 14, “Securing Your Application.”

Note Compacting cannot be done inside a transaction, or on a database that is currently open by another user.

Important Considerations When Compacting Replicated Databases

Replicated databases make extensive use of temporary space, especially when many design changes are made to an application. Just as with non-replicated databases, compacting regularly reduces file size and improves performance.

For optimal results, compact a replicated database twice. Compacting a replica the first time reclaims some space and flags other space as available for reclaiming later. Compacting a second time reclaims all available space. Compacting more than twice doesn’t provide additional benefits.

Improperly compacting the Design Master of a replica set causes the replica set to lose its Design Master. When Microsoft Access opens a replica, it stores the name and location of the file in the database. The next time Microsoft Access opens the replica, it checks to see if the file has the same name and location. If so, then the file opens normally. If not, Microsoft Access gives the replica a new ReplicaID property value and, if the file was the Design Master, removes the Design Master flag. This prevents two replicas from having the same ReplicaID property value or a replica set from having two Design Masters.

To prevent a Design Master from losing its Design Master flag when you compact a replicated database, do one of the following:

Open the Design Master database, and then click the Compact Database command on the Database Utilities submenu (Tools menu). When you do this, Microsoft Access compacts to a temporary file, and only deletes the original file and renames the temporary file to the original file name if the compact operation is successful.
If you prefer to save the original file as a backup, close the Design Master database before you use the Compact Database command, specify the Design Master as the database to compact from, and then specify a temporary file name to compact into, but do not open the database with the Open Database command (File menu) until you have renamed the compacted database back to its original file name. This also applies if you are using the CompactDatabase method in code to compact. The CompactDatabase method requires you to compact to a new file name. In both cases, if you open the temporary database before you rename it back to its original file name, Microsoft Access removes the Design Master flag.

Repairing a Database

If your database is damaged, close the database, then point to Database Utilities on the Tools menu and click Repair Database. You can also use the RepairDatabase method of the DBEngine object to repair your database. The RepairDatabase method checks all pages in the database for correct linkage, validates all system tables, and validates all indexes. Because the RepairDatabase method can’t fix all possible forms of database corruption, you should back up your database files regularly to avoid unrecoverable data loss. This kind of corruption can occur when the system isn’t shut down normally (such as during a power failure).

The RepairDatabase method takes only one argument, the name of the database file you want to repair. For example, to repair the Northwind database, use the following code:

DBEngine.RepairDatabase "C:\Program Files\Microsoft Office\Office" _
	& "\Samples\Northwind.mdb"

When a database is repaired, it may increase in size, because the process of creating indexes may leave some deleted pages in the database. It’s always a good idea to compact the database after any repair to eliminate unnecessary pages.