CompactDatabase Method

Applies To DBEngine object.

Description

Copies and compacts a closed database, and gives you the option of changing its version, collating order, and encryption. (Microsoft Jet workspaces only).

Syntax

DBEngine.CompactDatabase olddb, newdb, locale, options, password

The CompactDatabase method syntax has these parts.

Part	Description

olddb	A String that identifies an existing, closed database. It can be a full path and file name, such as "C:\db1.mdb". If the file name has an extension, you must specify it. If your network supports it, you can also specify a network path, such as "\\server1\share1\dir1\db1.mdb".
newdb	A String that is the file name (and path) of the compacted database that you're creating. You can also specify a network path. You can't use the newdb argument to specify the same database file as olddb.
locale	Optional. A Variant that is a string expression that specifies a collating order for creating newdb, as specified in Settings. If you omit this argument, the locale of newdb is the same as olddb.
	You can also create a password for newdb by concatenating the password string (starting with ";pwd=") with a constant in the locale argument, like this:
	dbLangSpanish & ";pwd=NewPassword"
	If you want to use the same locale as olddb (the default value), but specify a new password, simply enter a password string for locale:
	";pwd=NewPassword"
options	Optional. A constant or combination of constants that indicates one or more options, as specified in Settings. You can combine options by summing the corresponding constants.
password	Optional. A Variant that is a string expression containing a password, if the database is password protected. The string ";pwd=" must precede the actual password. If you include a password setting in locale, this setting is ignored.

Settings

You can use one of the following constants for the locale argument to specify the CollatingOrder property for string comparisons of text.

Constant	Collating order

dbLangGeneral	English, German, French, Portuguese, Italian, and Modern Spanish
dbLangArabic	Arabic
dbLangChineseSimplified	Simplified Chinese
dbLangChineseTraditional	Traditional Chinese
dbLangCyrillic	Russian
dbLangCzech	Czech
dbLangDutch	Dutch
dbLangGreek	Greek
dbLangHebrew	Hebrew
dbLangHungarian	Hungarian
dbLangIcelandic	Icelandic
dbLangJapanese	Japanese

(continued)

Constant	Collating order

dbLangKorean	Korean
dbLangNordic	Nordic languages (Microsoft Jet database engine version 1.0 only)
dbLangNorwDan	Norwegian and Danish
dbLangPolish	Polish
dbLangSlovenian	Slovenian
dbLangSpanish	Traditional Spanish
dbLangSwedFin	Swedish and Finnish
dbLangThai	Thai
dbLangTurkish	Turkish

You can use one of the following constants in the options argument to specify whether to encrypt or to decrypt the database while it's compacted.

Constant	Description

dbEncrypt	Encrypt the database while compacting.
dbDecrypt	Decrypt the database while compacting.

If you omit an encryption constant or if you include both dbDecrypt and dbEncrypt, newdb will have the same encryption as olddb.

You can use one of the following constants in the options argument to specify the version of the data format for the compacted database. This constant affects only the version of the data format of newdb and doesn't affect the version of any Microsoft Access-defined objects, such as forms and reports.

Constant	Description

dbVersion10	Creates a database that uses the Microsoft Jet database engine version 1.0 file format while compacting.
dbVersion11	Creates a database that uses the Microsoft Jet database engine version 1.1 file format while compacting.
dbVersion20	Creates a database that uses the Microsoft Jet database engine version 2.0 file format while compacting.
dbVersion30	Creates a database that uses the Microsoft Jet database engine version 3.0 file format (compatible with version 3.5) while compacting.

You can specify only one version constant. If you omit a version constant, newdb will have the same version as olddb. You can compact newdb only to a version that is the same or later than that of olddb.

Remarks

As you change data in a database, the database file can become fragmented and use more disk space than is necessary. Periodically, you can use the CompactDatabase method to compact your database to defragment the database file. The compacted database is usually smaller and often runs faster. You can also change the collating order, the encryption, or the version of the data format while you copy and compact the database.

You must close olddb before you compact it. In a multiuser environment, other users can't have olddb open while you're compacting it. If olddb isn't closed or isn't available for exclusive use, an error occurs.

Because CompactDatabase creates a copy of the database, you must have enough disk space for both the original and the duplicate databases. The compact operation fails if there isn't enough disk space available. The newdb duplicate database doesn't have to be on the same disk as olddb. After successfully compacting a database, you can delete the olddb file and rename the compacted newdb file to the original file name.

The CompactDatabase method copies all the data and the security permission settings from the database specified by olddb to the database specified by newdb.

If you use CompactDatabase to convert a version 1.x database to version 2.5 or 3.x, only applications using version Microsoft Jet 2.5 or 3.x can open the converted database.

Note In an ODBCDirect workspace, using the CompactDatabase method doesn't return an error, but instead loads the Microsoft Jet database engine into memory.

Caution Because the CompactDatabase method doesn't convert Microsoft Access objects, you shouldn't use CompactDatabase to convert a database containing such objects. To convert a database containing Microsoft Access objects, on the Tools menu, point to Database Utilities, and then click Convert Database.

See Also

CollatingOrder property, CreateDatabase method, Database object, RepairDatabase method.

Specifics (Microsoft Access)

To compact a database from the Microsoft Access window, point to Database Utilities on the Tools menu, and click Compact Database. Use the DAO CompactDatabase method to compact a database from Visual Basic code.

Note The CompactDatabase method won't completely convert a Microsoft Access database from one version to another. Only the data format is converted. Microsoft Access–defined objects, such as forms and reports, aren't converted. To convert a database created in a previous version, point to Database Utilities on the Tools menu and click Convert Database.

Example

This example uses the CompactDatabase method to change the collating order of a database. You cannot use this code in a module belonging to Northwind.mdb.

Sub CompactDatabaseX()

    Dim dbsNorthwind As Database

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    ' Show the properties of the original database.
    With dbsNorthwind
        Debug.Print .Name & ", version " & .Version
        Debug.Print "    CollatingOrder = " & .CollatingOrder
        .Close
    End With

    ' Make sure there isn't already a file with the
    ' name of the compacted database.
    If Dir("NwindKorean.mdb") <> "" Then Kill "NwindKorean.mdb"

    ' This statement creates a compact version of the
    ' Northwind database that uses a Korean language
    ' collating order.
    DBEngine.CompactDatabase "Northwind.mdb", 
        "NwindKorean.mdb", dbLangKorean

    Set dbsNorthwind = OpenDatabase("NwindKorean.mdb")

    ' Show the properties of the compacted database.
    With dbsNorthwind
        Debug.Print .Name & ", version " & .Version
        Debug.Print "    CollatingOrder = " & .CollatingOrder
        .Close
    End With

End Sub

This example uses the CompactDatabase method to change the version of a database. To run this code, you must have a Microsoft Jet version 1.1 database called Nwind11.mdb and you cannot use this code in a module belonging to Nwind11.mdb.

Sub CompactDatabaseX2()

    Dim dbsNorthwind As Database
    Dim prpLoop As Property

    Set dbsNorthwind = OpenDatabase("Nwind11.mdb")

    ' Show the properties of the original database.
    With dbsNorthwind
        Debug.Print .Name & ", version " & .Version
        Debug.Print "    CollatingOrder = " & .CollatingOrder
        .Close
    End With
    ' Make sure there isn't already a file with the
    ' name of the compacted database.
    If Dir("Nwind20.mdb") <> "" Then Kill "Nwind20.mdb"

    ' This statement creates a compact and encrypted
    ' Microsoft Jet 2.0 version of a Microsoft Jet version
    ' 1.1 database.
    DBEngine.CompactDatabase "Nwind11.mdb", _
        "Nwind20.mdb", , dbEncrypt + dbVersion20

    Set dbsNorthwind = OpenDatabase("Nwind20.mdb")

    ' Show the properties of the compacted database.
    With dbsNorthwind
        Debug.Print .Name & ", version " & .Version
        For Each prpLoop In .Properties
            On Error Resume Next
            If prpLoop <> "" Then Debug.Print "    " & _
                prpLoop.Name & " = " & prpLoop
            On Error GoTo 0
        Next prpLoop
        .Close
    End With

End Sub