CompactDatabase Method (JRO)

Access Developer Reference

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




SourceConnection   A String value specifying a connection to the source database to be compacted. An error will occur if the database specified by SourceConnection is already open.

DestConnection   A String value specifying a connection to the destination database to be created by the compaction. An error will occur if the database specified by DestConnection already exists or another file with that name already exists.


Connection properties are used to specify information for compacting the database. At a minimum, the Data Source property must be specified for both SourceConnection and DestConnection. The table below lists the connection properties that you can use with this method. All other connection properties will be ignored.

Property Source Destination Description
Provider Yes Yes Indicates the name of the provider to use to connect to the data source. If this property is not specified, the Microsoft.Jet.OLEDB.4.0 provider is assumed.

An error occurs if the name of the provider in the source string is different from the name of the provider in the destination string.

Data Source Yes Yes Indicates the name of the database. This property is required for both the source and destination connection information.
User Id Yes
Indicates the name of the user if the source database is secured.
Password Yes
Indicates the password for the user if the source database is secured.
Locale Identifier
Yes Indicates the locale id for the new database. If this is omitted, the destination database will have the General (0x00000409) locale id.

Locale specifies the collating order for string comparisons in the database. For destination database values, see the Remarks section.

Jet OLEDB:Database Password Yes Yes Indicates the password if the database is secured by a password.
Jet OLEDB:Engine Type Yes Yes Indicates the version of the source database to open or the version of the new database to be created. The OLE DB engine types that you can use are:

1 for JET Version 1.0

2 for JET Version 1.1

3 for JET Version 2.x

4 for JET Version 3.x

5 for JET Version 4.x

If this is omitted for the destination database, it will default to 5. The value for the destination database cannot be a version prior to that of the source database.

Jet OLEDB:Registry Path Yes
Sets information about the Windows Registry key that contains values for the Microsoft Jet Database Engine.
Jet OLEDB:System Database Yes
Indicates the location of the system database.
Jet OLEDB:Encrypt Database
Yes Indicates whether to encrypt the new database. If this parameter is omitted, the new database will have the same encryption as the source database.
Jet OLEDB:Don't Copy Locale on Compact
Yes Indicates that the database sort order should be used, overriding any per-column locale settings. (Boolean.)
Jet OLEDB:Compact Without Relationships
Yes Indicates whether to copy relationships to the new database. (Boolean.)
Jet OLEDB:Compact Without Replica Repair
Yes Indicates whether to try and find other replicas to repair damaged replicated databases. (Boolean.)

It is not possible to do an "in place" compact, that is, one in which the source database and destination database are the same, using this method. An error will occur if the provider does not support compacting Access databases.


The following table lists the destination database and Locale Identifier (LCID) values:

Description LCID
Chinese Punctuation 0x00000804
Chinese Stroke Count 0x00020804
Chinese Stroke Count (Taiwan) 0x00000404
Chinese Bopomofo (Taiwan) 0x00030404
Croatian 0x0000041a
Czech 0x00000405
Estonian 0x00000425
French 0x0000040c
General 0x00000409
Georgian Modern 0x00010437
German Phone Book 0x00010407
Hungarian 0x0000040e
Hungarian Technical 0x0001040e
Icelandic 0x0000040f
Japanese 0x00000411
Japanese Unicode 0x00010411
Korean 0x00000412
Korean Unicode 0x00010412
Latvian 0x00000426
Lithuanian 0x00000427
Macedonian (FYROM) 0x0000042f
Norwegian/Danish 0x00000414
Polish 0x00000415
Romanian 0x00000418
Slovak 0x0000041b
Slovenian 0x00000424
Spanish Traditional 0x0000040a
Spanish Modern 0x00000c0a
Swedish/Finnish 0x0000041d
Thai 0x0000041e
Turkish 0x0000041f
Ukrainian 0x00000422
Vietnamese 0x0000042a
Bb237197.vs_note(en-us,office.12).gif  Note
The General sort order contains the following languages: Africaans, Albanian, Arabic, Basque, Bulgarian, Byelorussian, Catalan, Dutch, English, Faeroese, Farsi, German - Standard, Greek, Hebrew, Hindi, Indonesian, Italian, Malay, Portuguese, Russian, Serbian, Swahili, Urdu.


This example demonstrates how to compact and encrypt a database with the CompactDatabase method.

  Public Sub CompactAndEncrypt()

Dim je As New JRO.JetEngine

' Make sure that a file doesn't exist with the name of ' the compacted database. If Dir("C:\Program Files\Microsoft Office" & _ "Office\Samples\Northwind2.mdb") <> "" Then Kill _ "C:\Program Files\Microsoft Office\Office\Samples\Northwind2.mdb"

' Compacts and encrypts version Northwind database. je.CompactDatabase _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Program Files\Microsoft Office" & _ "Office\Samples\Northwind.mdb", _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Program Files\Microsoft Office" & _ "Office\Samples\Northwind2.mdb;" & _ "Jet OLEDB:Encrypt Database=True"

End Sub