Setting Options

You use the options argument of the CompactDatabase method to specify various operations for Microsoft Jet to perform as part of the compact process. Using the options argument, you can encrypt and decrypt database files, and specify the version of the data format for the compacted database. You cannot use the CompactDatabase method to convert a database to a previous version.

Caution Because the CompactDatabase method doesn’t convert Microsoft Access-specific objects such as forms, reports, macros, and modules, you shouldn’t use the CompactDatabase method to convert a database containing such objects. To convert a database containing Microsoft Access objects, open the database in Microsoft Access and use the Convert Database command (Tools menu, Database Utilities submenu).

The following table lists the constants you can use in the options argument.

If you omit an encryption constant or include both dbDecrypt and dbEncrypt, the new database will have the same encryption as the old database.

See Also For more information on database encryption and Microsoft Jet security features, see Chapter 10, “Managing Security.”

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.

Note that this code calls the CanOpenDbExclusively function defined in Chapter 2, “Introducing Data Access Objects.” This function determines whether the database can be open exclusively for compacting.

Function CompactDb(strDbPath As String) As Boolean
	Dim dbs As Database
	Dim intLength As Integer
	Dim varPosition As Variant
	Dim strDbTemp As String, strDbCompacted As String
	Dim strDbBackup As String
	Dim strMsg As String
	Const conPermissionDenied As Integer = 70
	
	On Error GoTo Err_CompactDb
	' Initialize string for message.
	strMsg = "Database " & strDbPath & " cannot be opened exclusively. " _
		& "The database may have already been opened by you or another user."

	' Compact the database to a temporary file.
	intLength = Len(strDbPath)
	varPosition = InStr(strDbPath, ".mdb")
	If varPosition > 0 Then
		strDbTemp = Left(strDbPath, varPosition - 1)
		
		' Create backup file before compacting.
		strDbBackup = strDbTemp & ".bak"
		FileCopy strDbPath, strDbBackup
		
		' Check whether database can be opened exclusively.
		' This line calls a function defined in Chapter 2.
		If Not CanOpenDbExclusively(strDbPath) Then
			MsgBox strMsg
			GoTo Exit_CompactDb
		End If
		
		' Compact to new file.
		strDbCompacted = strDbTemp & "Compacted.mdb"
		DBEngine.CompactDatabase strDbPath, strDbCompacted
		
		' Delete uncompacted database.
		Kill strDbPath
		
		' Rename compacted database to original name.
		Name strDbCompacted As strDbPath
	End If
	CompactDb = True
	
Exit_CompactDb:
	On Error Resume Next
	dbs.Close
	Set dbs = Nothing
	Exit Function
	
Err_CompactDb:
	If Err = conPermissionDenied Then
		MsgBox strMsg
	Else
		MsgBox "Error " & Err & ": " & vbCrLf & Err.Description
	End If
	CompactDb = False
	Resume Exit_CompactDb
End Function

Tip If your hard disk is badly fragmented, it’s a good idea to defragment it using a program such as Microsoft Disk Defragmenter before compacting the database. This allows for the data to be written not only sequentially in the database, but also sequentially on disk should space allow.