CompactDatabase Statement

See AlsovbstmCompactDatabaseSee                 ExamplevbstmCompactDatabaseEx>Low


Compacts and encrypts or decrypts a Microsoft Access database.


CompactDatabase sourcename, destinationname [, locale [, options ] ]


The CompactDatabase statement uses the following parts:

Part                     Description


sourcename          A string expression1330R89 that is the name of an existing database.  It can be a fully qualified path, such as C:\MYDB.MDB.  The file-name extension, if applicable, is required.  If your network supports it, you can also specify a fully qualified network path, such as:

destinationname    A string expression that is the fully qualified path name of the new database to be created.  You can also specify a fully qualified network path, as with sourcename.  Cannot be the same name as sourcename.

locale                   A string expression specifying localization information for creating the destination database.  If you omit this part, the locale of destinationname is the same as sourcename.  See table below for locale constants.

options                 A Long1DDU1E5 numeric value indicating one or more options.  You can combine values by adding them together.  See the table below.


You can supply one of the following values (you must use the symbolic constants in DATACONS.TXT) in the locale part:

Constant                         Description


DB_LANG_GENERAL       English, German, French.

DB_LANG_SPANISH        Spanish, Italian.

DB_LANG_DUTCH           Dutch.

DB_LANG_SWEDFIN       Sweden, Finland.

DB_LANG_NORWDAN     Norway, Denmark.


DB_LANG_NORDIC         Nordic countries.


You can supply one or more of the following values or symbolic constants (from DATACONS.TXT) in the options part:

Constant                           Value        Definition


DB_VERSION10                    1            Create a Microsoft Access 1.0 database.

DB_ENCRYPT                      2            Encrypt the database.

DB_DECRYPT                      4            Remove encryption coding from the database.


If you omit the options part, newdatabase will have the same encryption state as curdatabase.  If both DB_ENCRYPT and DB_DECRYPT are set, newdatabase will have the same encryption state as curdatabase.

As you change data in a database, the file can become fragmented and use more disk space than necessary.  Periodically, you should compact your database in order to defragment the file.  The compacted database will usually be smaller.

The CompactDatabase statement essentially copies all the data and security permissions settings from one database into another.  In the process, the data in the resulting database is organized contiguously to recover disk-space.  It also gives you the option of changing the encryption or language locale during the compaction operation.

You must close a database before you compact it.  In a multiuser environment, you must make sure all other users have closed the database as well.  To make sure, you can open the database for exclusive use and then close it.

Since this statement creates a duplicate copy of the database, you must have enough disk space for both copies.  The compact operation fails if there isn't enough space available.  The compacted copy can be on a different disk.

After a database has been successfully compacted, you can delete the original database file and rename the compacted file to the original name.