DAO Database Collection FAQ

Last updated August 11, 2021. Copyright © 1999-2021 WINSOFT. All rights reserved.

try
  ShowMessage('DAO ' + GetDBEngine(dvDAO35).Version + ' installed');
except
  ShowMessage('DAO 3.5 not installed');
end;

try
  ShowMessage('DAO ' + GetDBEngine(dvDAO36).Version + ' installed');
except
  ShowMessage('DAO 3.6 not installed');
end;

try
  ShowMessage('ACEDAO ' + GetDBEngine(dvACE12).Version + ' installed');
except
  ShowMessage('ACEDAO not installed');
end;

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\ISAM Formats

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\ISAM Formats

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\ISAM Formats

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access\Access Connectivity Engine\ISAM Formats

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine

GetDBEngine.CreateDatabase('C:\database.mdb', dbLangGeneral, EmptyParam);

GetDBEngine.CompactDatabase('C:\database.mdb', 'C:\newdb.mdb', EmptyParam, EmptyParam, EmptyParam);

GetDBEngine.CompactDatabase('C:\database.mdb', 'C:\newdb.mdb', dbLangCzech, EmptyParam, EmptyParam);

GetDBEngine.CompactDatabase('C:\database.mdb', 'C:\newdb.mdb', '', dbEncrypt, EmptyParam);

GetDBEngine.CompactDatabase('C:\database.mdb', 'C:\newdb.mdb', '', dbDecrypt, EmptyParam)

// dbVersion10 - Jet 1.0 format

// dbVersion11 - Jet 1.1 format

// dbVersion20 - Jet 2.0 format

// dbVersion30 - Jet 3.0 and 3.5 formats

// dbVersion40 - Jet 4.0 format
GetDBEngine.CompactDatabase('C:\database.mdb', 'C:\newdb.mdb', '', dbVersion30, EmptyParam);

GetDBEngine.CompactDatabase('C:\database.mdb', 'C:\newdb.mdb', EmptyParam, EmptyParam, ';pwd=MyPassword');

GetDBEngine.RepairDatabase('C:\database.mdb');

DAODatabase.DeleteTable('MyTable');

DAODatabase.EraseTable('MyTable');

with DAODatabase.TableDefs do
  for i := 0 to Count - 1 do
    ShowMessage('Table: ' + Item[i].Name);

with DAODatabase.QueryDefs do
  for i := 0 to Count - 1 do
    ShowMessage('Query: ' + Item[i].Name);

procedure TForm1.DAODatasetDAOError(DataSet: TDataSet; E: EDatabaseError; var Action: TDataAction);
begin
  Action := daAbort;
end;

DAODataSet.SQL.Text := 'SELECT * FROM [Financial Reports]';

DAODataSet.Filter := 'MyDate = #10/14/00#';

DAODataSet.Filter := 'MyDate >= #10/14/00# and MyDate < #10/20/00#';

uses ActiveX;

procedure MyThread.Execute;
begin
  CoInitialize(nil);
  try
    // your code
  finally
    CoUninitialize;
  end;
end;

DAODataSet.Sort := 'Name DESC';

with DAODataSet do
begin
  ParamCheck := False;
  SQL.Clear;
  SQL.Add('SELECT Name');
  SQL.Add('FROM MyTable');
  ParamCheck := True;
  SQL.Add(''); // refreshing Params
  Open;
end;

DAODatabase.DatabaseConnect := ';pwd=Erik'; // database encrypted with password Erik

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Jet 3.5\Threads

function CreateDatabase(const Name: WideString; const Locale: WideString, Option: OleVariant): Database;

HRESULT CreateDatabase(const System::WideString Name, const System::WideString Locale, const System::OleVariant Option, _di_Database &CreateDatabase_result);

_di_Database db;
GetDBEngine()->CreateDatabase("C:\\database.mdb", dbLangGeneral, EmptyParam, db);

with DAODatabase.Handle do
begin
  TableDef := CreateTableDef('MyLinkTable', 0 {dbAttachedTable}, 'COUNTRY', ';DATABASE=C:\db.mdb');
  TableDefs.Append(TableDef);
end;

procedure TForm1.DAODataSet1TestTimeGetText(Sender: TField; var Text: string; DisplayText: Boolean);
begin
  Text := TimeToStr(Sender.AsDateTime);
end;

procedure TForm1.DAODataSet1TestTimeSetText(Sender: TField; const Text: string);
begin
  Sender.AsDateTime := StrToTime(Text);
end;

GetDBEngine.Idle(dbRefreshCache);

DAODatabase.ExecSQL('CREATE UNIQUE INDEX MyIndex ON MyTable (Name, Age)');

DAODatabase.ExecSQL('INSERT INTO DestTable SELECT * FROM SrcTable IN "" "dBase III;DATABASE=C:\"');

var TableDef: DAO.TableDef;

with DAODatabase.Handle do
begin
  TableDef := CreateTableDef('MyLinkedTable', EmptyParam, EmptyParam, EmptyParam);

  TableDef.Connect := 'dBase III;DATABASE=C:\';
  TableDef.SourceTableName := 'industry.dbf';

  TableDefs.Append(TableDef);
end;

with DAODatabase.Handle do
  Properties.Append(CreateProperty('PropertyName', dbText, 'PropertyValue', False));

DAODatabase.ChangePassword('OldPassword', 'NewPassword');

DAODatabase.ChangePassword(#0, 'NewPassword');

DAODataSet.TableName := 'COUNTRY$A2:C4';

DAODataSet.DatabaseConnect := 'Excel 8.0;HDR=NO';

DAODataset.DatabaseConnect := 'HTML Import';
DAODataset.DatabaseName := 'https://localhost/webpage.htm';

DAODataSet.SQL.Text := 'SELECT * FROM Country IN "" ";DATABASE=C:\SIMPLE.MDB;PWD=MyPassword"';
DAODataSet.Open;

with DAODatabase.Relations do
  for i := 0 to Count - 1 do
  begin
    ShowMessage('Relation name: ' + Item[i].Name);
    ShowMessage('Master table: ' + Item[i].Table);
    ShowMessage('Detail table: ' + Item[i].ForeignTable);
    ShowMessage('Fields: ');
    with Item[i].Fields do
      for j := 0 to Count - 1 do
        ShowMessage(Item[j].Name + ':' + Item[j].ForeignName);
  end;

with DAODataset.Recordset.Fields do
  for i := 0 to Count - 1 do
    ShowMessage(Item[i].Name);

with DAODatabase.Users do
  for i := 0 to Count - 1 do
    ShowMessage(Item[i].Name);

with DAODatabase.Groups do
  for i := 0 to Count - 1 do
    ShowMessage(Item[i].Name);

DAODatabase.CreateUser('NewUser', 'NewUserPID', 'NewUserPassword');
DAODatabase.DeleteUser('NewUser');

DAODatabase.CreateGroup('NewGroup', 'NewGroupPID');
DAODatabase.DeleteGroup('NewGroup');

DAODatabase.AddUserToGroup('NewUser', 'NewGroup');
DAODatabase.RemoveUserFromGroup('NewUser', 'NewGroup');

DAODatabase.Exclusive := True;
DAODatabase.Open;

DAODatabase.ExecSQL('SELECT * INTO MySheet IN "" "Excel 8.0;DATABASE=C:\MyFile.xls" FROM MyTable');

DAODatabase.ExecSQL('SELECT * INTO MyText#TXT IN "" "Text;DATABASE=C:\MyDir" FROM MyTable');

Format=Delimited(;)

Format=Delimited(,)

DAODatabase.TableDefs.Item['MyTable'].Fields.Item['OldFieldName'].Name := 'NewFieldName';

var
  Relation: DAO.Relation;
  Field: DAO.Field;
begin
  with DAODatabase.Handle do
  begin
    // create relation
    Relation := CreateRelation('MyRelation', 'MasterTable', 'DetailTable', 0);

    // add fields to relation (don't forget to set their ForeignName property)
    Field := Relation.CreateField('MasterID', dbInteger, 0);
    Field.ForeignName := 'DetailID';
    Relation.Fields.Append(Field);

    // append created relation to collection of all relations
    Relations.Append(Relation);
  end;
end;

var DAOField: DAO.Field;

DAOField := CreateField(Name, DAO.dbLong, EmptyParam);
DAOField.Attributes := DAOField.Attributes or dbAutoIncrField;

SELECT * FROM MyTable WHERE MyField = MyParameter

PARAMETERS ID LONG;
SELECT * FROM MyTable WHERE MasterID = ID

with DAODatabase1.Containers.Item['Databases'].Documents.Item['SummaryInfo'].Properties do
begin
  try ShowMessage('Title: ' + Item['Title'].Value); except end;
  try ShowMessage('Author: ' + Item['Author'].Value); except end;
  try ShowMessage('Company: ' + Item['Company'].Value); except end;
  try ShowMessage('Subject: ' + Item['Subject'].Value); except end;
  try ShowMessage('Manager: ' + Item['Manager'].Value); except end;
end;

MyComputer\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\3.5\Engines\Debug

SELECT * FROM MyTable WHERE Name LIKE 'T*'

SELECT * INTO MyDstTable IN "" ";DATABASE=db2.mdb"  FROM MySrcTable

INSERT INTO MyDstTable IN "" ";DATABASE=db2.mdb" SELECT * FROM MySrcTable

with GetDBEngine.Errors do
  for i := 0 to Count - 1 do
    with Item[i] do
      ShowMessage(
        'Description: ' + Description + #13 +
        'Number: ' + IntToStr(Number) + #13 +
        'Source: ' + Source + #13 +
        'HelpContext: ' + IntToStr(HelpContext) + #13 +
        'HelpFile: ' + HelpFile);

with DAODatabase.Handle.TableDefs.Item['MyTable'].Indexes do
  for i := 0 to Count - 1 do
  begin
    ShowMessage('Index: ' + Item[i].Name);
      with Item[i] do
        for j := 0 to Fields.Count - 1 do
          ShowMessage('Field: ' + Fields.Item[j].Name);
  end;

ShowMessage(DAODatabase.Handle.TableDefs.Item['MyTable'].DateCreated);

ShowMessage(DAODatabase.Handle.TableDefs.Item['MyTable'].LastUpdated);

var TableDef: DAO.TableDef;

with DAODatabase1.Handle do
begin
  TableDef := CreateTableDef('MyTable', EmptyParam, EmptyParam, EmptyParam);
  with TableDef do
    Fields.Append(CreateField('MyBoolField', DAO.dbBoolean, EmptyParam));
  TableDefs.Append(TableDef);
end;

var TableDef: DAO.TableDef;

with DAODatabase1.Handle do
begin
  TableDef := TableDefs.Item['MyTable'];
  with TableDef do
    Fields.Append(CreateField('MyBoolField', DAO.dbBoolean, EmptyParam));
end;

DAODataSet.Filter := 'MyDate=CDate("30.10.1999 10:20:30")'; // Slovak regional settings

with DAODatabase.Handle.TableDefs.Item['MyTable'].Fields.Item['MyBooleanField'] do
begin
  // try to remove old Format property
  try Properties.Delete('Format') except end;

  // create new Format property
  Properties.Append(CreateProperty('Format', dbText, 'True/False', EmptyParam));
end;

ShowMessage(DAODataSet.Recordset.Fields['MyField'].Properties['Caption'].Value);

ShowMessage(DAODataSet.Recordset.Fields.Item['MyField'].Properties.Item['Caption'].Value);

var
  i: Integer;
  Field: DAO.Field;

with DAODataSet.Recordset do
try
  Parent.BeginTrans;

  Field := Fields[0];

  for i := 1 to 1000 do
  begin
    AddNew;
    Field.Value := i;
    Update(dbUpdateRegular, False);
  end;

  Parent.CommitTrans(dbForceOSFlush);
except
  Parent.Rollback;
  raise;
end;

Dao::Recordset *Recordset = DAODataSet->Recordset;

try
{
  Recordset->Parent->BeginTrans();

  Variant Field = ((Variant)(IDispatch *)Recordset->Fields).OlePropertyGet("Item", 0);

  for (int i = 0; i < 1000; ++i)
  {
    Recordset->AddNew();
    Field.OlePropertySet("Value", i);
    Recordset->Update(dbUpdateRegular, false);
  }

  Recordset->Parent->CommitTrans(dbForceOSFlush);
}
catch (Exception &exception)
{
  Recordset->Parent->Rollback();
  throw;
}

// create query
DAODatabase.Handle.CreateQueryDef('MyQuery', 'SELECT * FROM MyTable');

// delete query
DAODatabase.Handle.DeleteQueryDef('MyQuery');

// enumerate queries
with DAODatabase.Handle do
  for i := 0 to QueryDefs.Count - 1 do
    ShowMessage(QueryDefs[i].Name + ': ' + QueryDefs[i].SQL);

DAODatabase.TableDefs.Refresh;

with DAODatabase do
  for i := 0 to Containers.Count - 1 do
    with Containers[i] do
      for j := 0 to Documents.Count - 1 do
        ShowMessage('Container: ' + Containers[i].Name + ' Document: ' + Documents[j].Name);

var Permissions: Integer;

Permissions := DAODatabase.Containers['Tables'].Documents['MyTable'].Permissions;
if Permissions = dbSecNoAccess then ShowMessage('NoAccess');
if Permissions and dbSecFullAccess = dbSecFullAccess then ShowMessage('FullAccess');
if Permissions and dbSecDelete = dbSecDelete then ShowMessage('Delete');
if Permissions and dbSecReadSec = dbSecReadSec then ShowMessage('ReadSec');
if Permissions and dbSecWriteSec = dbSecWriteSec then ShowMessage('WriteSec');
if Permissions and dbSecDBCreate = dbSecDBCreate then ShowMessage('DBCreate');
if Permissions and dbSecDBOpen = dbSecDBOpen then ShowMessage('DBOpen');
if Permissions and dbSecDBExclusive = dbSecDBExclusive then ShowMessage('DBExclusive');
if Permissions and dbSecDBAdmin = dbSecDBAdmin then ShowMessage('DBAdmin');
if Permissions and dbSecCreate = dbSecCreate then ShowMessage('Create');
if Permissions and dbSecReadDef = dbSecReadDef then ShowMessage('ReadDef');
if Permissions and dbSecWriteDef = dbSecWriteDef then ShowMessage('WriteDef');
if Permissions and dbSecRetrieveData = dbSecRetrieveData then ShowMessage('RetrieveData');
if Permissions and dbSecInsertData = dbSecInsertData then ShowMessage('InsertData');
if Permissions and dbSecReplaceData = dbSecReplaceData then ShowMessage('ReplaceData');
if Permissions and dbSecDeleteData = dbSecDeleteData then ShowMessage('DeleteData');

with DAODatabase.Containers['Tables'].Documents['MyTable'] do
begin
  UserName := 'MyUserName';
  MyPermissions := Permissions;
end;

DAODatabase.Containers['Tables'].Documents['MyTable'].Permissions := dbSecFullAccess;

with DAODatabase.Containers['Tables'].Documents['MyTable'] do
begin
  UserName := 'MyUserName';
  Permissions := dbSecFullAccess;
end;

SELECT * FROM MyTable IN "C:\MyDB.mdb"

SELECT * INTO DestTable IN "C:\DestDB.mdb" FROM SrcTable
SELECT * INTO DestTable FROM SrcTable IN "C:\SrcDB.mdb"

INSERT INTO DestTable IN "C:\DestDB.mdb" SELECT * FROM SrcTable
INSERT INTO DestTable SELECT * FROM SrcTable IN "C:\SrcDB.mdb"

DAODatabase.TableDefs['OldTableName'].Name := 'NewTableName';

with DAODatabase.TableDefs['MyTable'].Fields['MyField'] do
try
  Properties['UnicodeCompression'].Value := -1; // 0 switches off Unicode compression
except
  Properties.Append(CreateProperty('UnicodeCompression', dbBoolean, -1, False)); // 0 switches off Unicode compr.
end;

DAODatabase.Users.Item['UserName'].NewPassword('OldPassword', 'NewPassword');

DAODatabase.TableDefs['MyTable'].Fields['MyField'].Required := True;

var
  Field: DAO.Field;

Field := TableDef.CreateField('Name', DAO.dbText, 24);
Field.DefaultValue := 'Erik';
TableDef.Fields.Append(Field);

var
  Field: DAO.Field;

Field := TableDef.CreateField('Size', DAO.dbInteger, EmptyParam);
Field.ValidationRule := '>= 10 AND <= 1000';
Field.ValidationText := 'Size must be between 10 and 1000';
TableDef.Fields.Append(Field);

with DAODatabase.Handle.TableDefs.Item['MyTable'].Fields.Item['MyDateField'] do
begin
  // try to remove old Format property
  try Properties.Delete('Format') except end;

  // create new Format property
  Properties.Append(CreateProperty('Format', dbText, 'Short Date', EmptyParam));
end;

with DAODatabase.Handle.TableDefs['MyLinkedTable'] do
begin
  ShowMessage('Current connect: ' + Connect);

  // change connect
  Connect := 'Excel 5.0;HDR=YES;IMEX=2;DATABASE=' + GetCurrentDir + '\MyData.XLS';

  // refresh link
  RefreshLink;

  ShowMessage('New connect: ' + Connect);
end;