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)?
Just received an answer from Embarcadero:
Dmitry Arefiev wrote:
This is a known issue. At moment TFDMSAccessService does not really support avAccess2007.