Problem in compacting Access 2007 Database, After Compacting Access 2007 Database gets converted to Access 2002-2003 format

Question

I am using a Delphi procedure to Compact Access Database

Code sniphet of procedure is:

procedure CompactDatabase(pFullDatabasePathName : string; pLoginName : string = '';    pPassword : string = ''; pSystemDb : string = '');

var
  JE            : TJetEngine;

  sdbTemp       : String;

  sdbTempConn   : String;

  sdbSrcConn    : String;

  loginString   : String;

  systemDbString: String;

  compactDone   : Boolean;

  const
    SProviderAccess2007 = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=';

    SEngine = ';Jet OLEDB:Engine Type=';
    Access2007EngineType = '5';

 begin

  loginString := '';

  if (pLoginName <> '') then
    loginString := ';User Id= ' + pLoginName + '; Password = ' + pPassword;

  if ((pLoginName = '') and (pPassword <> '')) then
    loginString := ';Jet OLEDB:Database Password = ' + pPassword;


  systemDbString := '';

  if (pSystemDb <> '') then
    systemDbString := ';Jet OLEDB:System Database = ' + pSystemDb;


    try
      compactDone := False;
      JE := TJetEngine.Create(Application);

      sdbTemp := ExtractFileDir(pFullDatabasePathName) + '\TEMP' + ExtractFileName(pFullDatabasePathName);

      if FileExists(sdbTemp) then
        DeleteFile(sdbTemp);


      //Access 2007
      if not compactDone then
        begin
          try
            sdbSrcConn := SProviderAccess2007 + pFullDatabasePathName + loginString + systemDbString + SEngine + Access2007EngineType;
            sdbTempConn := SProviderAccess2007 + sdbTemp + SEngine + Access2007EngineType;
          JE.CompactDatabase(sdbSrcConn, sdbTempConn);
            compactDone := True;
          except
          end;
        end;

      if not compactDone then
        Raise Exception.Create('Compacting of database: ' + pFullDatabasePathName + 'failed!');

      if (not DeleteFile(pFullDatabasePathName)) then
        Raise Exception.Create('Compacting failed because cannot delete database: ' +  pFullDatabasePathName);

      if (not RenameFile(sdbTemp, pFullDatabasePathName)) then
        Raise Exception.Create('Compacting failed because cannot overwrite database: ' +  pFullDatabasePathName + ' by temporary compacted database: ' +   sdbTemp);

        if FileExists(sdbTemp) then
          DeleteFile(sdbTemp);

  finally
    JE.FreeOnRelease;
  end;
 end;

The outcome of this procedure is the Compact Access Database, but in Access 2002-2003 format.

I am unable to get the problem area. Is some dll or regsitry settings needs to be renewed? Please help...

Are you sure you are using an engine type 5? The connection string used in the CompactDatabase method description (msdn.microsoft.com/en-us/library/bb237197(office.12).aspx) is using "Provider=Microsoft.Jet.OLEDB.4.0;" Your code refers to "Microsoft.ACE.OLDDB.12.0" The highest jet engine version is 4. Version 12 is a long way off? — Marjan Venema
"Provider=Microsoft.Jet.OLEDB.4.0;" is used for Access 97/2000 access datbase, For Access2007, 'Provider=Microsoft.ACE.OLEDB.12.0' is used. — Nains
Strictly speaking, "Provider=Microsoft.Jet.OLEDB.4.0;" is for all Jet 4 databases, which means A2000, A2002, A2003, and any MDB file created in A2000 format or higher, including if it's created in A2007 or A2010. For ACCDB, you need to use the ACE, not Jet 4. Jet 4 knows nothing about ACCDB format, which didn't exist until the introduction of the ACE with A2007. — David-W-Fenton

Nains Nains · Accepted Answer · 2010-10-06T09:32:29

Thanks 4 ur support, I found a working solution to this problem and found it worth sharing with you.

Now instead of using Jet 4, I am now using 'DAO.DBEngine.120'.

  //Access 2007 
  if not compactDone then 
    begin 
      try
       // DaoAccess2007 is OleVariant Type

        DaoAccess2007 := CreateOleObject('DAO.DBEngine.120');
        DaoAccess2007.CompactDatabase(pFullDatabasePathName,sdbTemp); 
        compactDone := True; 
      except 
      end; 
    end;

The outcome is the Compacted Databsae in Access 2007 Format. Cheers!

Problem in compacting Access 2007 Database, After Compacting Access 2007 Database gets converted to Access 2002-2003 format

1 Answers

Now instead of using Jet 4, I am now using 'DAO.DBEngine.120'.