Board index » delphi » Compact Database for MS Access in Delphi 5.0

Compact Database for MS Access in Delphi 5.0

Hi All,

   Anyone knows how to call the Compact Database function in Delphi 5.0 in
order to compact database for Microsoft Access. Any help is appreciate.
Thanks.

Best regards,
Wilson

 

Re:Compact Database for MS Access in Delphi 5.0


Quote
>  Anyone knows how to call the Compact Database function in Delphi 5.0 in
>order to compact database for Microsoft Access. Any help is appreciate.

You need to import the JRO type library to use the following code

Here's a procedure that works for me.  You have to import the JRO type
library to compact an Access database.

procedure TMainForm.CompactDataBa{*word*224}uClick(Sender: TObject);
var JROJetEngine: TJROJetEngine;
    strTempDBPath,
    strSource,
    strDest,
    strJetVersion: string;
    iLength: integer;
begin
  if strGlobalAccessVersion = '2000' then
    strJetVersion := '5'  { Access 2000}
  else if strGlobalAccessVersion = '97' then
    strJetVersion := '4';   {Jet 3.x, Access 97}
  strTempDBPath := ExtractFilePath(strGlobalDBPath) + 'TEMP.MDB';
  strSource := 'Data Source=' + strGlobalDBPath + ';Jet OLEDB:Engine Type='
+ strJetVersion;
  strDest := 'Data Source=' + strTempDBPath + ';Jet OLEDB:Engine Type=' +
strJetVersion;
  try
    try
      Screen.Cursor := crHourGlass;
      if not dbModule.CloseAll then
        begin
          MessageDlg('Error closing some datasets, unable to compact',
mtError, [mbOk], 0);
          exit;
        end
      else
        dbModule.ADOConnection1.Close;
      Application.ProcessMessages;
      JROJetEngine := TJROJetEngine.Create(Application);
      JROJetEngine.CompactDatabase(strSource, strDest);
      SysUtils.DeleteFile(strGlobalDBPath);
      RenameFile(strTempDBPath, strGlobalDBPath);
      MessageDlg('The database has been packed', mtInformation, [mbOk], 0);
    except
      on E: Exception do
        MessageDlg('Error packing database: ' + E.Message, mtError, [mbOk],
0);
    end;
  finally
    JROJetEngine.Free;
    dbModule.ADOConnection1.Open;
    Screen.Cursor := crDefault;
  end;
end;
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Compact Database for MS Access in Delphi 5.0


Brian,

    I wonder how are you going to get the Microsoft Access's version number?
You have variable calls strGlobalAccessVersion? How you got this? Thanks.

Regards,
Wilson

"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:ra81gtkgle0rhov75rtl49enk4bd8rbtg1@4ax.com...

Quote

> >  Anyone knows how to call the Compact Database function in Delphi 5.0 in
> >order to compact database for Microsoft Access. Any help is appreciate.

> You need to import the JRO type library to use the following code

> Here's a procedure that works for me.  You have to import the JRO type
> library to compact an Access database.

> procedure TMainForm.CompactDataBa{*word*224}uClick(Sender: TObject);
> var JROJetEngine: TJROJetEngine;
>     strTempDBPath,
>     strSource,
>     strDest,
>     strJetVersion: string;
>     iLength: integer;
> begin
>   if strGlobalAccessVersion = '2000' then
>     strJetVersion := '5'  { Access 2000}
>   else if strGlobalAccessVersion = '97' then
>     strJetVersion := '4';   {Jet 3.x, Access 97}
>   strTempDBPath := ExtractFilePath(strGlobalDBPath) + 'TEMP.MDB';
>   strSource := 'Data Source=' + strGlobalDBPath + ';Jet OLEDB:Engine
Type='
> + strJetVersion;
>   strDest := 'Data Source=' + strTempDBPath + ';Jet OLEDB:Engine Type=' +
> strJetVersion;
>   try
>     try
>       Screen.Cursor := crHourGlass;
>       if not dbModule.CloseAll then
>         begin
>           MessageDlg('Error closing some datasets, unable to compact',
> mtError, [mbOk], 0);
>           exit;
>         end
>       else
>         dbModule.ADOConnection1.Close;
>       Application.ProcessMessages;
>       JROJetEngine := TJROJetEngine.Create(Application);
>       JROJetEngine.CompactDatabase(strSource, strDest);
>       SysUtils.DeleteFile(strGlobalDBPath);
>       RenameFile(strTempDBPath, strGlobalDBPath);
>       MessageDlg('The database has been packed', mtInformation, [mbOk],
0);
>     except
>       on E: Exception do
>         MessageDlg('Error packing database: ' + E.Message, mtError,
[mbOk],
> 0);
>     end;
>   finally
>     JROJetEngine.Free;
>     dbModule.ADOConnection1.Open;
>     Screen.Cursor := crDefault;
>   end;
> end;
> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:Compact Database for MS Access in Delphi 5.0


Hi,
I've tried the procedure, as outlined, but it seems to be not
reopening the ADOConnection as in the snip:-
Quote
>   finally
>     JROJetEngine.Free;
>     dbModule.ADOConnection1.Open;
>     Screen.Cursor := crDefault;
>   end;
> end;

 David

"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:ra81gtkgle0rhov75rtl49enk4bd8rbtg1@4ax.com...

Re:Compact Database for MS Access in Delphi 5.0


David,

   I have not tried to run this procedure, cause I am still waiting for
Brian to know him how to he gets the Access Version for the variable of
strGlobalAccessVersion. Thanks.

Regards,
Wilson

Quote
"David" <p...@citydrive.co.uk> wrote in message news:3b0177fc$1_2@dnews...
> Hi,
> I've tried the procedure, as outlined, but it seems to be not
> reopening the ADOConnection as in the snip:-
> >   finally
> >     JROJetEngine.Free;
> >     dbModule.ADOConnection1.Open;
> >     Screen.Cursor := crDefault;
> >   end;
> > end;
>  David

> "Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
> news:ra81gtkgle0rhov75rtl49enk4bd8rbtg1@4ax.com...

Re:Compact Database for MS Access in Delphi 5.0


Quote
>I've tried the procedure, as outlined, but it seems to be not
>reopening the ADOConnection as in the snip:-

I can not reproduce that.  Do you have your LoginPrompt set to true?  You will
have to present a password after compact if it is not in your connection string.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Compact Database for MS Access in Delphi 5.0


Quote
>    I wonder how are you going to get the Microsoft Access's version number?
>You have variable calls strGlobalAccessVersion? How you got this? Thanks.

Prompt the user or check the registry for the version of Jet installed.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Compact Database for MS Access in Delphi 5.0


Hi,
LoginPrompt set to false
David

"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:0nr3gt0urpt1ac9clf3dflthgcg3bsor6n@4ax.com...

Quote

> >I've tried the procedure, as outlined, but it seems to be not
> >reopening the ADOConnection as in the snip:-

> I can not reproduce that.  Do you have your LoginPrompt set to true?
You will
> have to present a password after compact if it is not in your
connection string.

> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:Compact Database for MS Access in Delphi 5.0


Quote
>LoginPrompt set to false

Then you may be missing that you need to login again after reconnecting.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Compact Database for MS Access in Delphi 5.0


I'm obviously missing something fundemental here - brain in neutral -
When I run the compiled app the db is indeed compressed, however I can
then only access some forms others produce an EoleException 'operation
not allowed when object closed' I also get this when I try to exit
app.
I assumed my ADOConnection connected prop := false but this being the
case why are some ADOds avilable and some not?
"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:sn76gt89ompsdq17alq4gvb0nhd7or5vsk@4ax.com...
Quote

> >LoginPrompt set to false
> Then you may be missing that you need to login again after
reconnecting.

> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:Compact Database for MS Access in Delphi 5.0


Quote
>I assumed my ADOConnection connected prop := false but this being the
>case why are some ADOds avilable and some not?

None of the components connected to your TadoConnection are open after you close
it.  Maybe you are not reopening all the datasets you try to use after you
reopen the Tadoconnection.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Compact Database for MS Access in Delphi 5.0


Hi,
I've checked following;
ADOConnection.open
ADOConnection.active
DMDs.active
All to no avail still get 'Operation is not allowed when the object is
closed'
I have realised however that the only Form.Ds I can access was created
by showmodal method; whilst the Forms.ds that give the error were
created by create(self) method I cannot think what the relavence is ?
David

Quote
> None of the components connected to your TadoConnection are open
after you close
> it.  Maybe you are not reopening all the datasets you try to use
after you
> reopen the Tadoconnection.

> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:Compact Database for MS Access in Delphi 5.0


Quote
>I have realised however that the only Form.Ds I can access was created
>by showmodal method; whilst the Forms.ds that give the error were
>created by create(self) method I cannot think what the relavence is ?
>David

Have you tried explicitly closing all your forms before the compact then
recreating them?

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Compact Database for MS Access in Delphi 5.0


Hi,
The main form is an SDI,fsnormal it only has the various menu's on
it.The Ds.forms are created on Menu.option.click and freed on
closing.The only Form open,or in memory, when I compress is the Main
form and a couple of dialoge forms(About eg).
I'm not sure therefore how else to impliment yr suggestion
David

Quote
> Have you tried explicitly closing all your forms before the compact
then
> recreating them?

> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:Compact Database for MS Access in Delphi 5.0


Hi, Further to my last post;
I tried setting the Ds.Active = false at the beginning of the Compress
procedure and Ds.Active = true at the end of procedure this resolves
the errors with the Ds object.
However I'm left with the err msg when trying to Exit the App from the
main form, which I cannot see how to resolve.
David

Quote

> The main form is an SDI,fsnormal it only has the various menu's on
> it.The Ds.forms are created on Menu.option.click and freed on
> closing.The only Form open,or in memory, when I compress is the Main
> form and a couple of dialoge forms(About eg).
> I'm not sure therefore how else to impliment yr suggestion
> David

> > Have you tried explicitly closing all your forms before the
compact
> then
> > recreating them?

> > --
> > Brian Bushay (TeamB)
> > Bbus...@NMPLS.com

Other Threads