[FAQ's: OD] How do I perform a Compact & Repair on my database?

[RobDog888]

Compacting and Repairing helps to keep your database in top running condition.

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.

Compacting optimizes the performance of both Access databases and Access projects.

You can perform a Compact and Repair on your database manually four ways or at least one way via code.


A Compact and Repair can be performed automatically every time you close your database...
Set the option in the Options menu when you have your database open.
Go to Tools > Options > General tab > check the Compact on Close check box.


To Compact and Repair a currently open database manually...
Go to Tools > Database Utilities > click Compact and Repair Database/Project.


To Compact and Repair a closed database manually...
1. Go to Tools > Database Utilities > click Compact and Repair Database.
2. In the Database to Compact From dialog box, specify the Access file you want to compact, and then click Compact.
3. In the Compact Database Into dialog box, specify a name, drive, and folder for the compacted Access file. Click the Save button.


A Compact can be done easily via a commandline switch of "/compact".
/compact - Compacts and repairs the Access database, or compacts the Access project specified before the /compact option and then closes Access. You can execute it from the Run dialog or from another program using the VB 6 Shell method or ShellExecute API for example.



To Compact And/Or Repair via code...

VB 6 Code Example:

Option Explicit
'Add a reference to MS Access xx.0 Object Library
Private Sub Command1_Click()
 
    On Error Goto MyError
 
    Dim oApp As Access.Application
 
    Set oApp = New Access.Application
    oApp.DBEngine.CompactDatabase "C:\RobDog888.mdb", "C:\RobDog888Cpd.mdb"
    Kill "C:\RobDog888.mdb"
    Name "C:\RobDog888Cpd.mdb" As "C:\RobDog888.mdb"
    oApp.DBEngine.RepairDatabase "C:\RobDog888.mdb"
    MsgBox "Compact Complete!", vbOkOnly
    oApp.Quit acQuitSaveNone
    Set oApp = Nothing
    Exit Sub
 
MyError:
    MsgBox Err.Number & " - " & Err.Description, vbOkOnly
End Sub

To Compact & Repair via code...

VB 6 Code Example:

Option Explicit
'Add a reference to MS Access xx.0 Object Library
Private Sub Command1_Click()
 
    On Error GoTo MyError
 
    Dim oApp As Access.Application
 
    Set oApp = New Access.Application
    oApp.CompactRepair "C:\RobDog888.mdb", "C:\RobDog888CRd.mdb", False
    Kill "C:\RobDog888.mdb"
    Name "C:\RobDog888CRd.mdb" As "C:\RobDog888.mdb"
    MsgBox "Compact & Repair Complete!", vbOKOnly
    oApp.Quit acQuitSaveNone
    Set oApp = Nothing
    Exit Sub
 
MyError:
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly
End Sub

[RobDog888]

To do a C & R on an Access database using ADO/ADO.NET...

Classic VB 6:
http://support.microsoft.com/kb/230501/

Visual Basic.NET:
http://support.microsoft.com/default...b;en-us;306287