Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

Packing Access Database

I know I've seen this before, but the Search function in EE has been going haywire and I'm getting timeouts. So I'm asking again, how do I pack an Access database?
0
DragonSlayer
Asked:
DragonSlayer
  • 3
1 Solution
 
Mohammed NasmanSoftware DeveloperCommented:
Hello

 Here's what you asked for :)

uses ComObj;
procedure TForm1.Button1Click(Sender: TObject);
var
  dao: OLEVariant;
begin
  dao := CreateOleObject('DAO.DBEngine.35');
  dao.CompactDatabase('C:\My Documents\DB1.mdb',
'C:\My Documents\CompactedDB1.mdb');
end;

Best regards
Mohammed Nasman
0
 
Mohammed NasmanSoftware DeveloperCommented:
also here's how to compact access 2000 database, I didn't test it cuz i'm home and i dont' have access 2000 in home

//=======

Function CompactAndRepair(sOldMDB : String; 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;


Example :

if CompactAndRepair('e:\Old.mdb', 'e:\New.mdb') then
   ShowMessage('Successfully')
else
   ShowMessage('Error?');

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


Mohammed
0
 
CrazyOneCommented:


implementation

{$R *.DFM}

   const
  ODBC_ADD_DSN = 1;

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


procedure CompactMDB;
var
  sCompactDB, sCompactedDB: string;
 
begin
  sCompactDB := 'C:\DataBaseDir\TheDataBase.mdb';
  sCompactedDB := 'C:\DataBaseDir\CompactedTheDataBase.mdb';
  if not SQLConfigDataSource(0, ODBC_ADD_DSN,
    'Microsoft Access Driver (*.mdb)', PChar(
    'COMPACT_DB=' + sCompactDB + ' ' + sCompactedDB + ' General'#0)) then
     ShowMessage('Problems with compacting ' + sCompactDB + ' could not compact');
end;

end.


The Crazy One
0
 
Mohammed NasmanSoftware DeveloperCommented:
Hello DragonSlayer

  I test the second code I gave to you, it's work fine with Acess2000 and AccessXP (2002), so now you can Compact anydatabase with it :)

Mohammed
0
 
DragonSlayerAuthor Commented:
Thanks mnasman!

Sorry crazyone, but mnasman's a tad bit faster... and it doesn't require linking to external DLL.

mnasman, how do I know whether or not the database is currently opened by other programmes, because I open it as ShareReadWrite.
Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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