>

CompactDatabase Method

Applies To

DBEngine Object.

Description

Copies, compacts, and gives you the option of changing the version, collating order, and encryption of a closed database.

Syntax

DBEngine.CompactDatabase olddb, newdb [, locale [, options [, ú;pwd=password]]]

The CompactDatabase method syntax has these parts.

Part

Description

olddb

A string expression that identifies an existing, closed database. It can be a full path and filename, such as "C:\MYDB.mdb". If the filename has an extension, you must specify it. If your network supports it, you can also specify a network path, such as "\\MYSERVER\MYSHARE\MYDIR\MYDB.mdb".

newdb

A string expression that is the full path of the compacted database that you're creating. You can also specify a network path as with olddb. You can't use the newdb argument to specify the same database file as olddb.

locale

A string expression used to specify collating order for creating newdb, as specified in Settings. If you omit this argument, the locale of newdb is the same as olddb.

options

An integer that indicates one or more options, as specified in Settings. You can combine options by summing the corresponding constants.

password

An optional string argument containing a password, if the database is password protected. The string ";pwd=" must precede the actual password.


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

dbLangCyrillic

Russian

dbLangCzech

Czech

dbLangDutch

Dutch

dbLangGreek

Greek

dbLangHebrew

Hebrew

dbLangHungarian

Hungarian

dbLangIcelandic

Icelandic

dbLangNordic

Nordic languages (Microsoft Jet database engine version 1.0 only)

dbLangNorwdan

Norwegian and Danish


Constant

Collating order

dbLangPolish

Polish

dbLangSpanish

Traditional Spanish

dbLangSwedfin

Swedish and Finnish

dbLangTurkish

Turkish


You can use one of the following constants in the options argument to specify whether to encrypt or to decrypt the database as 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 while compacting.

dbVersion11

Creates a database that uses the Jet database engine version 1.1 while compacting.

dbVersion20

Creates a database that uses the Jet database engine version 2.0 while compacting.

dbVersion30

Creates a database that uses the Jet database engine version 3.0 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 necessary. Periodically, you can compact your database to defragment the database file. The compacted database is usually smaller and often runs faster. You can also choose to change the collating order, the encryption, or the version of the data format while you copy and compact the database.

The CompactDatabase method copies all the data and the security permissions settings from the database specified by olddb to the database specified by newdb. In the process, the data in the compacted database is organized contiguously to recover disk space.

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 filename.

See Also

CollatingOrder Property, CreateDatabase Method, Database Object.

Specifics (Microsoft Access)

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.

Use the Convert Database command on the File menu to convert Microsoft Access databases from an earlier version to the current version.

Example

This example uses CompactDatabase to compact a database and keeps a backup copy of the original database. The new database has the same locale as the source database, but is encrypted.


DBEngine.CompactDatabase "C:\Biblio.mdb","C:\BibNew.mdb","",dbEncrypt
...
Kill "C:\Biblio.BAK"
Name "C:\Biblio.mdb" As "C:\BIBLIO.BAK"
Name "C:\BibNew.mdb" As "C:\Biblio.mdb"
This example copies a database named Northwind.mdb and keeps a new, compacted copy named COPY.NEW. The new database is encrypted.


DBEngine.CompactDatabase "C:\Northwind.mdb", "C:\Copy.NEW","",dbEncrypt