Compacting a Database

As a database file is used, it can become fragmented as objects and records are created and deleted. Periodic defragmentation reduces the amount of wasted space in the file and can enhance performance. Compacting can also repair a corrupted database.

The following listings demonstrate how to compact a database.

Note   Use JRO, not ADO to compact a database.

DAO

Sub DAOCompactDatabase()

   ' Make sure there isn't already a file with the

   ' name of the compacted database.

   If Dir(".\NewNorthWind.mdb") <> "" Then Kill ".\NewNorthWind.mdb"

   ' Basic compact - creating new database named newnwind

   DBEngine.CompactDatabase ".\NorthWind.mdb", ".\NewNorthWind.mdb"

   ' Delete the original database

   Kill ".\NorthWind.mdb"

   ' Rename the file back to the original name

   Name ".\NewNorthWind.mdb" As ".\NorthWind.mdb"

End Sub

JRO

Sub JROCompactDatabase()

   Dim je As New JRO.JetEngine

   ' Make sure there isn't already a file with the

   ' name of the compacted database.

   If Dir(".\NewNorthWind.mdb") <> "" Then Kill ".\NewNorthWind.mdb"

   ' Compact the database

   je.CompactDatabase "Data Source=.\NorthWind.mdb;", _

      "Data Source=.\NewNorthWind.mdb;"

   ' Delete the original database

   Kill ".\NorthWind.mdb"

   ' Rename the file back to the original name

   Name ".\NewNorthWind.mdb" As ".\NorthWind.mdb"

End Sub

The JRO CompactDatabase method takes two connection strings that indicate the source database and destination database respectively. See the JRO online help for more information on the JRO CompactDatabase method.

In addition to defragmenting or repairing your database, CompactDatabase can also be used to change the database password, convert the database from an older Microsoft Jet version to a new version, to encrypt or decrypt the database, or to change the locale of the database. The following code demonstrates how to encrypt a database.

DAO

Sub DAOEncryptDatabase()

   ' Use compact to create a new, encrypted version of the database

   DBEngine.CompactDatabase ".\NorthWind.mdb", _

      ".\NewNorthWind.mdb", , dbEncrypt

End Sub

JRO

Sub JROEncryptDatabase()

   Dim je As New JRO.JetEngine

   ' Use compact to create a new, encrypted version of the database

   je.CompactDatabase "Data Source=.\NorthWind.mdb;", _

      "Data Source=.\NewNorthWind.mdb;" & _

      "Jet OLEDB:Encrypt Database=True"

End Sub