Compact an Access database (Views: 706)
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. |