Compact an Access database

Problem/Question/Abstract:

Using D6 Pro, Access XP and Jet 4.0 Sp6 - how can I compact Access files?

Answer:

This does it:

procedure TMainForm.ActionCompactAccessDBExecute(Sender: TObject);
var
  JetEngine: Variant;
  TempName: string;
  aAccess: string;
  stAccessDB: string;
  SaveCursor: TCursor;
begin
  stAccessDB := 'Provider = Microsoft.Jet.OLEDB.4.0;' +
    'Data Source = %s;Jet OLEDB: Engine type = ';
  stAccessDB := stAccessDB + '5'; {5 for Access 2000 and 4 for Access 97}
  OpenDialog1.InitialDir := oSoftConfig.ApplicationPath + 'Data\';
  OpenDialog1.Filter := 'MS Access (r) (*.mdb)|*.mdb';
  if OpenDialog1.execute and (uppercase(ExtractFileExt
    (OpenDialog1.FileName)) = '.MDB') then
  begin
    if MessageDlg('This process can take several minutes. Please wait till the end ' +
      #13 + #10 + 'of it. Do you want to proceed? Press No to exit.', mtInformation,
      [mbYes, mbNo], 0) = mrNo then
      exit;
    SaveCursor := screen.cursor;
    screen.cursor := crHourGlass;
    aAccess := OpenDialog1.FileName;
    TempName := ChangeFileExt(aAccess, '.$$$');
    DeleteFile(PChar(TempName));
    JetEngine := CreateOleObject('JRO.JetEngine');
    try
      JetEngine.CompactDatabase(Format(stAccessDB, [aAccess]),
        Format(stAccessDB, [TempName]));
      DeleteFile(PChar(aAccess));
      RenameFile(TempName, aAccess);
    finally
      JetEngine := Unassigned;
      screen.cursor := SaveCursor;
    end;
  end;
end;

Important Notes:
Include the JRO_TLB unit in your uses clause.
Nobody should use or open the database during compacting.
If the compiler gives you an error on the JRO_TLB unit follow these steps:
Using the Delphi IDE go to Project – Import Type Library.
Scroll down until you reach “Microsoft Jet and Replication Objects 2.1 Library”.
Click on Install button.
Recompile a gain.