Compact an Access database (Views: 97)


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


This does it:

procedure TMainForm.ActionCompactAccessDBExecute(Sender: TObject);
  JetEngine: Variant;
  TempName: string;
  aAccess: string;
  stAccessDB: string;
  SaveCursor: TCursor;
  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
    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
    SaveCursor := screen.cursor;
    screen.cursor := crHourGlass;
    aAccess := OpenDialog1.FileName;
    TempName := ChangeFileExt(aAccess, '.$$$');
    JetEngine := CreateOleObject('JRO.JetEngine');
      JetEngine.CompactDatabase(Format(stAccessDB, [aAccess]),
        Format(stAccessDB, [TempName]));
      RenameFile(TempName, aAccess);
      JetEngine := Unassigned;
      screen.cursor := SaveCursor;

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.

<< Back to main page