Deleteing, Compacting, & Repairing

I found this code - which doesn't seem to work with ADO
(Although it compiles and I can call it, I just keep getting the 'Repair not successful.' & 'Compression not successful' messages)


function SQLConfigDataSource(hwndParent: HWND; fRequest: WORD; lpszDriver: LPCSTR;
   lpszAttributes: LPCSTR): BOOL; stdcall; external'ODBCCP32.DLL';

procedure TfrmData.DatabasePack;
var OldDB,NewDB: string;
const ODBC_ADD_DSN=1;
begin
   {compress and repair the database}
   with dbMain do
   begin
    dbMain.Close;
     OldDB:= ChangeFileExt(Application.ExeName, '.mdb');
     NewDB:= ChangeFileExt(Application.ExeName, '.mdb');
     if not SQLConfigDataSource(0,ODBC_ADD_DSN,'Microsoft Access Driver (*.mdb)',
       PChar('REPAIR_DB='+OldDB+#0)) then
       MessageDlg('Repair not successful.',mtInformation,[mbOK],0)
       else MessageDlg('Repair successful.',mtInformation,[mbOK],0);
     if not SQLConfigDataSource(0,ODBC_ADD_DSN,'Microsoft Access Driver (*.mdb)',
       PChar('COMPACT_DB='+OldDB+' '+NewDB+' General'#0)) then
       MessageDlg('Compression not successful.',mtInformation,[mbOK],0)
       else MessageDlg('Compression successful.',mtInformation,[mbOK],0);
     dbMain.Open;
   end;
end;

Easier said then done. The database has 6 tables with Referential integrity
Master Detail/detail/detail
I need a solution to empty the tables and then pack/repair

I am increasing the points to 500

novicedelphidev,
you could create a completely new database via runtime instead of > empty the tables and reset the auto index values

Are you agree ?

I have a working example about creating a new Access database at runtime. If you need I will provide it to you ....

Hi

OldDB:= ChangeFileExt(Application.ExeName, '.mdb');
NewDB:= ChangeFileExt(Application.ExeName, '.mdb');


Shouldn't these be different names.........

Also, compacting and repairing a database will not reset any auto increments or remove any data, all they do is free up space allocated to deleted records.

Also does referential integrity work outside of the Access IDE?

>> If your tables have referential integrity then you should empty it using bottom up approach.

This is correct.......


Voodooman

Hi

>> AtulParmar

Is incorrect in suggesting you use DAO as you clearly say that you are using the ADO.


Voodooman

This code will do the trick in my app.

Code:

function TfrmMain.CompactAndRepair(sOldMDB, sNewMDB: String): Boolean;
const
  sProvider = 'Provider=Microsoft.Jet.OLEDB.4.0;';
var
  oJetEng : JetEngine;
begin
  sOldMDB := sProvider + 'Data Source=' + sOldMDB;
  sNewMDB := sProvider + 'Data Source=' + sNewMDB;

  try
    oJetEng := CoJetEngine.Create;
    oJetEng.CompactDatabase(sOldMDB,sNewMDB);
    oJetEng := Nil;
    Result := True;
  except
    oJetEng := Nil;
    Result := False;
  end;
end;

To empty your table(s) with Parent/Child/etc. structure...

Assign a referential delete constraint to them so you are allowed to delete the parent and all related children will be removed automatically.

Hi,

I created an application to run queries in a Access database and then compact and repair when it is finished.

I have Delphi 7 Enterprise and thus have the servers component tab. I use a TAccessAplication component in my app.

Here is the code with the Database name as Input.

procedure TForm1.CompactRepairExecute(dbname : string);
var curs : TCursor;
begin
  curs := forms.Screen.Cursor;
  forms.Screen.Cursor := crHourGlass;
  busy := true;
  try
    ForceDirectories('c:\temp');
   
    if AccessApplication1.CompactRepair(dbname,'c:\temp\CandR.mdb',true)
      then
        begin
          DeleteFile(dbname);
          MoveFile(pchar('c:\temp\CandR.mdb'),pchar(dbname));
          DeleteFile('c:\temp');
        end
      else
        ShowMessage('Error Compacting File');
  finally
    forms.Screen.Cursor := curs;
  end;

end;


Hope It Helps