Board index » delphi » Problems compacting Access 2000 Database

Problems compacting Access 2000 Database

I am having trouble packing an Access 2000 database which has a workgroup
(security) file.  I get the message:

"You do not have the necessary permissions to use the '' object.  Have your
system admin....."

I am using the technique suggested by dozens of posts over the years, and it
worked fine until I put security onto the database.

Specifically, I have set up the following constants:

  ProviderStr               = 'Provider=Microsoft.Jet.OLEDB.4.0';
  PasswordStr               = 'Password=';
  UserIDStr                 = 'User ID=';
  DataSourceStr             = 'Data Source=';
  SysDatabaseStr            = 'Jet OLEDB:System database=';
  JetEngineStr              = 'Jet OLEDB:Engine Type=5';

I have a function that produces a connection string:

function ConnectionStr( const UserName: String;
                              const UserPwd: String;
                              const DatabaseName: String): String;
var
  CompleteStr: String;
begin
  CompleteStr := ProviderStr                   + ';' +
                 PasswordStr    + UserPWD      + ';' +
                 UserIDStr      + UserName     + ';' +
                 DataSourceStr  + DatabaseName + ';' +
                 SysDatabaseStr + AccessWG     + ';' +
                 JetEngineStr;
  // AccessWG is eg C:\Temp\MyDB.mdw
  Result := CompleteStr;
end;

I use this function to provide the connections string to my TAdoConnection
and I can open the database and use it successfully.  So it seems to have
everything I need.

But when I run the compact routine, it falls over:

procedure CompactDB;
var JROJetEngine: TJetEngine;
    strSource,
    strDest: string;
begin
  // AccessDB is eg C:\Temp\MyDB.mdb
  // AccessDBPath is eg C:\Temp\
  strSource := ConnectionStr(PackUser, PackUserPwd, AccessDB );
  strDest   := ConnectionStr(PackUser, PackUserPwd,
AccessDBPath+'Temp.mdb' );
  try
    try
      adoPOS.Connected := false;
      Application.ProcessMessages;
      JROJetEngine := TJetEngine.Create(Application);
      JROJetEngine.CompactDatabase(strSource, strDest);
      SysUtils.DeleteFile(AccessDB);
      RenameFile(strTempDBPath, AccessDB);
    except
      on E: Exception do
        MessageDlg( 'Error packing database: ' + E.Message,
                    mtError, [mbOk], 0);
    end;
  finally
    JROJetEngine.Free;
  end;
end;

PackUser and PackUserPwd are valid, and I can log into the database as this
user and compact it normally.

Any suggestions much appreciated!

TIA
Rob

 

Re:Problems compacting Access 2000 Database


Quote
>PackUser and PackUserPwd are valid, and I can log into the database as this
>user and compact it normally.

>Any suggestions much appreciated!

My guess is you need to specify the System Database in the destination
connection string as well as the source connection string.
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Problems compacting Access 2000 Database


Brian,

Thanks, but I did have the system database in the destination string.

However, I've just worked it out, although I don't understand *why* it
works, but at least it is working!

I have four groups in my database: Admins, Users, SpecialUsers and
SpecialAdmins.  I have removed all rights from Users and Admins, and made
SpecialAdmins have "Admin" rights and SpecialUsers have reduced rights.

I created my "PackUser" user, and made it a member of Users and
SpecialAdmins.  With this configuration, the compact/repair didn't work
through Delphi, although it did directly thought Access.

When I made "PackUser" a member of Admins, it did work.

Thanks for you help.  I guess if you had $10 for every time you've replied
to a "How do I compact an Access database" question you'd be rich by now!
<g>

Cheers
Rob

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

Quote
> >PackUser and PackUserPwd are valid, and I can log into the database as
this
> >user and compact it normally.

> >Any suggestions much appreciated!
> My guess is you need to specify the System Database in the destination
> connection string as well as the source connection string.
> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Other Threads