DAO Database Collection FAQ

Last updated March 22, 2017. Copyright © 1999-2017 WINSOFT. All rights reserved.
How can I determine DAO version?
  ShowMessage('DAO ' + GetDBEngine(dvDAO35).Version + ' installed');
  ShowMessage('DAO 3.5 not installed');

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

  ShowMessage('ACEDAO ' + GetDBEngine(dvACE12).Version + ' installed');
  ShowMessage('ACEDAO not installed');
You can use also DAO Detect utility supplied with DAO components.
What is the newest DAO version?
DAO 3.6 and ACEDAO 15.0
Where can I download the newest Microsoft Access Runtime?
Microsoft Access 2016 Runtime
What DAO ISAM drivers are available on my computer?
See registration database using REGEDIT.EXE program.

List of Jet 3.5 ISAM drivers (used by DAO 3.5, DAO 3.51):
List of Jet 4.0 ISAM drivers (used by DAO 3.6):
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\ISAM Formats
ACEDAO ISAM drivers:
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
Where can I find further DAO information?
DAO documentation: DAO35.HLP file in folder C:\Program Files\Common Files\Microsoft Shared\DAO.
Newsgroup: microsoft.public.vb.database.dao at msnews.microsoft.com
Microsoft Jet Database Engine
Microsoft Access Tips for Serious Users
ACC97: CommitTrans Help Topic Uses dbFlushOSCacheWrites Constant
HOWTO: Redistributing DAO with Your Visual C++ 5.0 Application
ACC97: How to Read .LDB Files
PRB: Error "The Jet VBA File... Failed to Initialize When Called"
PRB: The Jet VBA File VBAJet32.dll Failed to Initialize
How To Redistribute DAO 3.6
PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60
HOWTO: Ensure Jet 3.5 Is Installed Correctly (Part I)
HOWTO: Ensure Jet 3.5 Is Installed Correctly (Part II)
How do I create a new Access database?
GetDBEngine.CreateDatabase('C:\database.mdb', dbLangGeneral, EmptyParam);
How do I compact an Access database?
GetDBEngine.CompactDatabase('C:\database.mdb', 'C:\newdb.mdb', EmptyParam, EmptyParam, EmptyParam);
You can use additional parameter for specifying destination locale:
GetDBEngine.CompactDatabase('C:\database.mdb', 'C:\newdb.mdb', dbLangCzech, EmptyParam, EmptyParam);
For data encryption/decryption use dbEncrypt/dbDecrypt constants:
GetDBEngine.CompactDatabase('C:\database.mdb', 'C:\newdb.mdb', '', dbEncrypt, EmptyParam);

GetDBEngine.CompactDatabase('C:\database.mdb', 'C:\newdb.mdb', '', dbDecrypt, EmptyParam)
For database format conversion specify new database format:
// 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);
For password protected database specify password as last parameter:
GetDBEngine.CompactDatabase('C:\database.mdb', 'C:\newdb.mdb', EmptyParam, EmptyParam, ';pwd=MyPassword');
How do I repair a corrupted database?
How do I delete a table from the Acess database?
How do I delete all records from the table?
How do I find all available tables and queries at runtime?
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);
How to avoid showing DAO exceptions?
procedure TForm1.DAODatasetDAOError(DataSet: TDataSet; E: EDatabaseError; var Action: TDataAction);
  Action := daAbort;
What limitations has Access database?
Access 2016 specifications
MS Access 2007 and MS Access 2010
Older MS Access
How do I specify multiword table names?
Enclose multiword names into [ ] brackets:
DAODataSet.SQL.Text := 'SELECT * FROM [Financial Reports]';
How do I specify date values?
Enclose date values into ## characters:
DAODataSet.Filter := 'MyDate = #10/14/00#';

DAODataSet.Filter := 'MyDate >= #10/14/00# and MyDate < #10/20/00#';
How do I use DAO components in multithreaded application?
Call CoInitialize and CoUninitialize in each thread:
uses ActiveX;

procedure MyThread.Execute;
    // your code
How do I specify index in DAODataSet component?
DAO components work like queries in MS Access, so you needn't to specify index. You specify Sort property and Jet Engine finds and uses proper index automatically. If suitable index doesn't exist, Jet Engine automatically sorts records, so such operation is not limited to indexed fields.
How do I sort records in descending order?
Add DESC clause after field name:
DAODataSet.Sort := 'Name DESC';
How do I create multiline SQL statement at runtime?
Disable parameter checking using ParamCheck property:
with DAODataSet do
  ParamCheck := False;
  SQL.Add('SELECT Name');
  SQL.Add('FROM MyTable');
  ParamCheck := True;
  SQL.Add(''); // refreshing Params
Why I get error message "Can't start your application. The workgroup information file is missing or opened exclusively by another user."?
Don't use DAODatabase.Password property for entering password of encrypted database, but specify it using DatabaseConnect property instead:
DAODatabase.DatabaseConnect := ';pwd=Erik'; // database encrypted with password Erik
Property DAODatabase.Password is used for user's password when user level security is enabled. Such passwords are stored in workgroup information file with .mdw extension.
How do I set number of threads in DAO 3.5?
Change this value in registry database with regedit.exe (default is 3):
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Jet 3.5\Threads
This value determines number of threads for internal use in Jet engine only and not for using in application.
How do I use DAO 3.5 functions in C++ Builder?
C++ Builder converts DAO interface unit, originally created in Delphi, so return value of Delphi function is converted as the last parameter of C++ function and C++ functions always return HRESULT type.

For example CreateDatabase function in declared in Delphi as:
function CreateDatabase(const Name: WideString; const Locale: WideString, Option: OleVariant): Database;
C++ Builder converts this function to:
HRESULT CreateDatabase(const System::WideString Name, const System::WideString Locale, const System::OleVariant Option, _di_Database &CreateDatabase_result);
To call this function, you need to add the last parameter:
_di_Database db;
GetDBEngine()->CreateDatabase("C:\\database.mdb", dbLangGeneral, EmptyParam, db);
All DAO functions (DAO 3.5 interface) are declared in dao.hpp file.
Why can't I link table from one mdb database to another?
dbAttachedTable constant is incorrectly defined in DAO, it should be zero:
with DAODatabase.Handle do
  TableDef := CreateTableDef('MyLinkTable', 0 {dbAttachedTable}, 'COUNTRY', ';DATABASE=C:\db.mdb');
Why can't I enter time without date into date/time field?
Delphi requires entering date into TDateTime fields. You should add SetText and GetText events to your TDateField field to enable entering and proper formatting of time values:
procedure TForm1.DAODataSet1TestTimeGetText(Sender: TField; var Text: string; DisplayText: Boolean);
  Text := TimeToStr(Sender.AsDateTime);

procedure TForm1.DAODataSet1TestTimeSetText(Sender: TField; const Text: string);
  Sender.AsDateTime := StrToTime(Text);
How do I refresh DAO 3.5 cache?
How do I create an index?
DAODatabase.ExecSQL('CREATE UNIQUE INDEX MyIndex ON MyTable (Name, Age)');
Can I use MS Access 95 databases with DAO 3.5?
Yes, MS Access 95 database format is compatible with DAO 3.5, so you can use MS Access 95 databases without any changes.
How do I import data from an external table?
DAODatabase.ExecSQL('INSERT INTO DestTable SELECT * FROM SrcTable IN "" "dBase III;DATABASE=C:\"');
Has DAO 3.5 support for record locking?
No DAO 3.5 always locks 2kB pages.
How can I link dBase table to MS Access database?
var TableDef: DAO.TableDef;

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

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

How do I create custom database property?
with DAODatabase.Handle do
  Properties.Append(CreateProperty('PropertyName', dbText, 'PropertyValue', False));
How can I change database password?
DAODatabase.ChangePassword('OldPassword', 'NewPassword');
Note: DAODatabase must be opened in Exclusive mode.
To remove database password, use empty string as a NewPassword.
To set a new database password using DAO 3.6:
DAODatabase.ChangePassword(#0, 'NewPassword');
How do I specify MS Excell range of cells to access?
DAODataSet.TableName := 'COUNTRY$A2:C4';
How do I switch off header in MS Excell worksheet?
DAODataSet.DatabaseConnect := 'Excel 8.0;HDR=NO';
What limitations have text tables?
Max fields in table: 255
Max size of field name: 64
Max field width: 32766
Max rows: 65000
Is it possible to use separate directories for database file and lock file?
Can I use HTML Import and HTML Export directly from/to my website?
Yes, you can specify website URL:
DAODataset.DatabaseConnect := 'HTML Import';
DAODataset.DatabaseName := 'http://localhost/webpage.htm';
Has DAO 3.5 support for multi-threading on Windows 95?
DAO 3.5 supports only Unicode interfaces for multi-threading. Because Microsoft Windows 95 does not fully suports Unicode, DAO can not be used in multiple threads on Windows 95.
Which threading model supports DAO 3.5?
DAO 3.5 supports OLE apartment threading model.
Can I use URL for specifying mdb database location?
How do I use heterogenous queries on databases protected with password?
DAODataSet.SQL.Text := 'SELECT * FROM Country IN "" ";DATABASE=C:\SIMPLE.MDB;PWD=MyPassword"';
How do I get a list of all relationships in a database?
with DAODatabase.Relations do
  for i := 0 to Count - 1 do
    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);
How do I get a list of all fields in a table?
with DAODataset.Recordset.Fields do
  for i := 0 to Count - 1 do
How do I get a list of all users?
with DAODatabase.Users do
  for i := 0 to Count - 1 do
How do I get a list of all user groups?
with DAODatabase.Groups do
  for i := 0 to Count - 1 do
How do I create or delete a new user or user group?
DAODatabase.CreateUser('NewUser', 'NewUserPID', 'NewUserPassword');

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

DAODatabase.AddUserToGroup('NewUser', 'NewGroup');
DAODatabase.RemoveUserFromGroup('NewUser', 'NewGroup');
How do I access an mdb database on CD/DVD?
Set exclusive access, so lock file (ldb) will not be created:
DAODatabase.Exclusive := True;
How do I export table to Excel worksheet?
DAODatabase.ExecSQL('SELECT * INTO MySheet IN "" "Excel 8.0;DATABASE=C:\MyFile.xls" FROM MyTable');
How do I export table to comma-delimited text file?
Use this code to export table to text format:
DAODatabase.ExecSQL('SELECT * INTO MyText#TXT IN "" "Text;DATABASE=C:\MyDir" FROM MyTable');
If comma-delimited text file is required, change line in SCHEMA.INI file from
and start export again.
How do I get a list of users currently connected to database?
Use Msldbusr.dll
ACC: Microsoft Jet Utilities Available in Download Center
ACC97: How to Read .LDB Files
How do I change name of field?
DAODatabase.TableDefs.Item['MyTable'].Fields.Item['OldFieldName'].Name := 'NewFieldName';
How do I change type of field?
This is not possible directly with DDL statements or DAO objects. You need to add a new field, copy data from old field to the new field (for example using UPDATE statement) and then delete old field.
How do I create a relation?
  Relation: DAO.Relation;
  Field: DAO.Field;
  with DAODatabase.Handle do
    // 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';

    // append created relation to collection of all relations
How do I create an autoincrement field?
var DAOField: DAO.Field;

DAOField := CreateField(Name, DAO.dbLong, EmptyParam);
DAOField.Attributes := DAOField.Attributes or dbAutoIncrField;
Why my parameterized query doesn't work?
Parameter names in SQL statements can't begin with ":"
SELECT * FROM MyTable WHERE MyField = MyParameter
How do I use master-detail relationship in queries with equal field names?
Use PARAMETERS clause to declare parameters to avoid parameter name conflict:
Is it possible to undelete deleted records in MS Access database?
How secure are password encrypted MS Access databases?
MS Acess databases aren't secure. Both database encryption password and admin passwords can be easily retrieved.
How do I get database properties like Title, Author and Company?
with DAODatabase1.Containers.Item['Databases'].Documents.Item['SummaryInfo'].Properties do
  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;
How can I improve DAO performance?
Use DML (Data Manipulation Language) SQL statements instead of loops. For example deleting records with SQL statement 'DELETE FROM MyTable' is much faster than deleting each record separately with TDAOTable.Delete statement in loop. Jet database engine contains very sophisticated cost-based query optimizer. You can activate function ShowPlan to see created plan of execution for each query:
Create this key in registry database:
Under this key create a new string value with name JETSHOWPLAN and data ON.
You can later turn off this function by setting data to OFF.
How do I select records where some field begins with given character? My SQL statement "SELECT * FROM MyTable WHERE Name = 'T%'" doesn't work.
Use LIKE operator:
How do I copy table to another Access database?
Connect DAODataSet to the first database (db1.mdb). Then use this SQL statement:
SELECT * INTO MyDstTable IN "" ";DATABASE=db2.mdb"  FROM MySrcTable
To append records to the existing table in the second database use:
INSERT INTO MyDstTable IN "" ";DATABASE=db2.mdb" SELECT * FROM MySrcTable
How do I retrieve DAO error code?
Use this code, you can place it in OnDAOError event handler:
with GetDBEngine.Errors do
  for i := 0 to Count - 1 do
    with Item[i] do
        'Description: ' + Description + #13 +
        'Number: ' + IntToStr(Number) + #13 +
        'Source: ' + Source + #13 +
        'HelpContext: ' + IntToStr(HelpContext) + #13 +
        'HelpFile: ' + HelpFile);
How do I convert MS Access database to newer format?
Use CompactDatabase method, see How do I compact an Access database?
How do I get a list of table indexes?
with DAODatabase.Handle.TableDefs.Item['MyTable'].Indexes do
  for i := 0 to Count - 1 do
    ShowMessage('Index: ' + Item[i].Name);
      with Item[i] do
        for j := 0 to Fields.Count - 1 do
          ShowMessage('Field: ' + Fields.Item[j].Name);
How do I get the date/time when the table was created?
How do I get the date/time when the table was last modified?
How do I create a new table with Boolean field?
var TableDef: DAO.TableDef;

with DAODatabase1.Handle do
  TableDef := CreateTableDef('MyTable', EmptyParam, EmptyParam, EmptyParam);
  with TableDef do
    Fields.Append(CreateField('MyBoolField', DAO.dbBoolean, EmptyParam));
How can I add a new Boolean field to existing table?
var TableDef: DAO.TableDef;

with DAODatabase1.Handle do
  TableDef := TableDefs.Item['MyTable'];
  with TableDef do
    Fields.Append(CreateField('MyBoolField', DAO.dbBoolean, EmptyParam));
How do I use regional-formatted date/time values?
Use MS Access CDate function:
DAODataSet.Filter := 'MyDate=CDate("30.10.1999 10:20:30")'; // Slovak regional settings
How do I create 'True/False' formatted boolean field?
Set Format property of this field to 'True/False' value:
with DAODatabase.Handle.TableDefs.Item['MyTable'].Fields.Item['MyBooleanField'] do
  // try to remove old Format property
  try Properties.Delete('Format') except end;

  // create new Format property
  Properties.Append(CreateProperty('Format', dbText, 'True/False', EmptyParam));
How do I retrieve caption of the field?
DAO 3.6:
DAO 3.5:
How can I directly use DAO for very fast record insertion?
Delphi code:
  i: Integer;
  Field: DAO.Field;

with DAODataSet.Recordset do

  Field := Fields[0];

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

C++ Builder code:
Dao::Recordset *Recordset = DAODataSet->Recordset;


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

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

catch (Exception &exception)
How do I create/delete/enumerate Access queries?
// create query
DAODatabase.Handle.CreateQueryDef('MyQuery', 'SELECT * FROM MyTable');

// delete query

// enumerate queries
with DAODatabase.Handle do
  for i := 0 to QueryDefs.Count - 1 do
    ShowMessage(QueryDefs[i].Name + ': ' + QueryDefs[i].SQL);
How do I refresh TableDefs collection?
How do I retrieve Access containers and documents?
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);
How do I retrieve table permissions?
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');
Use UserName property to retrieve permissions for specified user or group:
with DAODatabase.Containers['Tables'].Documents['MyTable'] do
  UserName := 'MyUserName';
  MyPermissions := Permissions;
How do I change table permissions?
DAODatabase.Containers['Tables'].Documents['MyTable'].Permissions := dbSecFullAccess;
Use UserName property to change permissions for specified user or group:
with DAODatabase.Containers['Tables'].Documents['MyTable'] do
  UserName := 'MyUserName';
  Permissions := dbSecFullAccess;
How do I retrieve records from external MS Access database?
SELECT * FROM MyTable IN "C:\MyDB.mdb"
Insert records to a new table:
SELECT * INTO DestTable IN "C:\DestDB.mdb" FROM SrcTable
SELECT * INTO DestTable FROM SrcTable IN "C:\SrcDB.mdb"
Insert records to an existing table:
INSERT INTO DestTable IN "C:\DestDB.mdb" SELECT * FROM SrcTable
INSERT INTO DestTable SELECT * FROM SrcTable IN "C:\SrcDB.mdb"
How do I rename a table?
DAODatabase.TableDefs['OldTableName'].Name := 'NewTableName';
How can I set Unicode Compression for given field?
with DAODatabase.TableDefs['MyTable'].Fields['MyField'] do
  Properties['UnicodeCompression'].Value := -1; // 0 switches off Unicode compression
  Properties.Append(CreateProperty('UnicodeCompression', dbBoolean, -1, False)); // 0 switches off Unicode compr.
How can I change an existing user's password?
DAODatabase.Users.Item['UserName'].NewPassword('OldPassword', 'NewPassword');
How can I set Required field attribute?
DAODatabase.TableDefs['MyTable'].Fields['MyField'].Required := True;
How do I create a new field with default value?
  Field: DAO.Field;

Field := TableDef.CreateField('Name', DAO.dbText, 24);
Field.DefaultValue := 'Erik';
How do I create a new field with validation rule?
  Field: DAO.Field;

Field := TableDef.CreateField('Size', DAO.dbInteger, EmptyParam);
Field.ValidationRule := '>= 10 AND <= 1000';
Field.ValidationText := 'Size must be between 10 and 1000';
How do I set "Format" property to "Short Date" for a Date/Time field?
with DAODatabase.Handle.TableDefs.Item['MyTable'].Fields.Item['MyDateField'] do
  // try to remove old Format property
  try Properties.Delete('Format') except end;

  // create new Format property
  Properties.Append(CreateProperty('Format', dbText, 'Short Date', EmptyParam));
How do I update connection information for a linked table?
with DAODatabase.Handle.TableDefs['MyLinkedTable'] do
  ShowMessage('Current connect: ' + Connect);

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

  // refresh link

  ShowMessage('New connect: ' + Connect);