• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 473
  • Last Modified:

Deleteing, Compacting, & Repairing

I would like to empty certain tables, and perform a compact & repair on them.

Reason being is I need empty the tables and reset the auto index values.

I am using Delphi 7.0 + ADO + MS ACCESS

What is the best method of doing this via runtime (code)?

Sample code please!

I will start this off at 100 points, but will increase it significantly for a good solution!
0
novicedelphidev
Asked:
novicedelphidev
  • 3
  • 2
  • 2
  • +3
2 Solutions
 
novicedelphidevAuthor Commented:
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;
0
 
VoodoomanCommented:
Hi

To reset the autoindex on an empty table is easy in access.


Delete the recs in the table and use an Insert Statement to insert the start value of the auto increment column at 1 less than your start point and then delete it - bingo you have reset the auto increment.

To start at 1 insert a rec with a value of  0 and delete it.

To start at 1000 insert a record with a value of 999 and delete it.


Voodooman
0
 
novicedelphidevAuthor Commented:
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

e.g.  if Master Detail1/detail2/detail3
then first empty detail3 then detail2 then detail1 and then master

then try the following

var
   dao: OLEVariant;
begin
   dao := CreateOLEObject('DAO.DBEngine.35'); //
   dao.CompactDatabase(Old.MDB, New.MDB, ';LANGID=0x0409;CP=1252;COUNTRY=0', 32, '');
   dao := UnAssigned;
end;
0
 
esoftbgCommented:
novicedelphidev,
you could create a completely new database via runtime instead of > empty the tables and reset the auto index values

Are you agree ?
0
 
esoftbgCommented:
I have a working example about creating a new Access database at runtime. If you need I will provide it to you ....
0
 
VoodoomanCommented:

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
0
 
VoodoomanCommented:

Hi

>> AtulParmar

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


Voodooman
0
 
JeePeeTeeCommented:
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;
0
 
JeePeeTeeCommented:
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.


0
 
vatamyCommented:
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
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now