databasedelphims-access-2010delphi-xe7firedac

Delphi FireDAC with MS Access 2010 database. Why does it convert ACE to Jet?


I have converted a .mdb database to a .accdb database by following these steps: https://support.office.com/en-us/article/Convert-a-database-to-the-accdb-file-format-69abbf06-8401-4cf3-b950-f790fa9f359c (using MS Access 2010)

After the conversion, the .accdb file starts with the following: (database.accdb, file header viewed with hex editor), which is what I intended...

00 01 00 00 53 74 61 6E 64 61 72 64 20 41 43 45 20 44 42 00 02 00 00 00 B5 6E 03 62 60 09 C2 55 E9 A9 67 72 40 3F 00 9C 7E 9F 90 FF 85 9A 31 C5
....Standard ACE DB.....µn.b`.ÂUé©gr@?.œ~Ÿ.ÿ…š1Å

After opening the database, dropping a table, re-creating the table and doing some inserts with TFDConnection / TFDPhysMSAccessDriverLink / TFDBatchMove / TFDBatchMoveDataSetReader / TFDBatchMoveDataSetWriter and the following code

  FAccessDB := TFDConnection.Create(Self);
  FAccessDB.Name := '';
  FAccessDB.Params.Clear;
  FAccessDB.Params.Add('DriverID=MSAcc_Direct');
  FAccessDB.LoginPrompt := False;

  // FDPhysMSAccessDriverLink1
  FFDPhysMSAccessDriverLink1 := TFDPhysMSAccessDriverLink.Create(Self);
  FFDPhysMSAccessDriverLink1.Name := '';
  FFDPhysMSAccessDriverLink1.DriverID := 'MSAcc_Direct';

  // Table_Out
  FFDTable_Out := TFDTable.Create(Self);
  FFDTable_Out.Name := '';
  FFDTable_Out.Connection := FAccessDB;

  // FDBatchMove1
  FFDBatchMove1 := TFDBatchMove.Create(Self);
  FFDBatchMove1.Name := '';
  FFDBatchMove1.OnError := FDBatchMove1Error;
  FFDBatchMove1.OnFindDestRecord := FDBatchMove1FindDestRecord;
  FFDBatchMove1.OnProgress := FDBatchMove1Progress;

  // FDBatchMoveDataSetReader1
  FFDBatchMoveDataSetReader1 := TFDBatchMoveDataSetReader.Create(Self);
  FFDBatchMoveDataSetReader1.Name := '';

  // FDBatchMoveDataSetWriter1
  FFDBatchMoveDataSetWriter1 := TFDBatchMoveDataSetWriter.Create(Self);
  FFDBatchMoveDataSetWriter1.Name := '';

  // FDBatchMove1
  FFDBatchMove1.Reader := FFDBatchMoveDataSetReader1;
  FFDBatchMove1.Writer := FFDBatchMoveDataSetWriter1;
  FFDBatchMove1.Options := [poIdentityInsert];

  FAccessDB.Params.Values['Database'] := 'database.accdb';
  FAccessDB.Connected := True;
  aDropTableSQL := 'DROP TABLE ' + FTablenameDest;
  FAccessDB.ExecSQL(aDropTableSQL);
  FAccessDB.Commit;
  aCreateTableSQL := 'CREATE TABLE ' + FTablenameDest; // plus the rest 
                                              //of the create statement

  FFDTable_Out.TableName := FTablenameDest;
  FFDTable_Out.Active := True;
  FFDBatchMoveDataSetReader1.DataSet := FDataSetSrc; // a TDataset from 
                                                       // another database 
  FFDBatchMoveDataSetWriter1.DataSet := FFDTable_Out;
  FFDBatchMoveDataSetWriter1.Direct := True;

  FFDBatchMoveDataSetReader1.DataSet.Active := True;
  FFDBatchMoveDataSetWriter1.DataSet.Active := True;

  FFDBatchMove1.Mode := dmAlwaysInsert;
  FFDBatchMove1.Execute;
  FAccessDB.Commit;
  FAccessDB.Connected := False;

  FFDMSAccessService1 := TFDMSAccessService.Create(Self);
  FFDMSAccessService1.Name := '';

  FFDMSAccessService1.Database := 'database.accdb';
  FFDMSAccessService1.DestDatabase := 'database.accdb_temp.accdb';

  FFDMSAccessService1.DBVersion := avAccess2007;

  FFDMSAccessService1.Compact;   // <-- seems to convert here...

the file header of database.accdb becomes

00 01 00 00 53 74 61 6E 64 61 72 64 20 4A 65 74 20 44 42 00 01 00 00 00 B5 6E 03 62 60 09 C2 55 E9 A9 67 72 40 3F 00 9C 7E 9F 90 FF 85 9A 31 C5
....Standard Jet DB.....µn.b`.ÂUé©gr@?.œ~Ÿ.ÿ…š1Å

again, which it also was before conversion from .mdb to .accdb

it seems to me, that 'Standard Jet DB' means old format (.mdb) and 'Standard ACE DB' means new format (.accdb)

Does FireDAC convert it back? why? How can I keep the new Access Format (.accdb, ACE DB)?


Solution

  • Just received an answer from Embarcadero:

    Dmitry Arefiev wrote:

    This is a known issue. At moment TFDMSAccessService does not really support avAccess2007.