Scalabium Software

SMExport/SMImport suites
Knowledge for your independence'.
Home Delphi and C++Builder tips

#111: How can I create table in MS Access with DAO

In this tip I want to describe how you can in run-time create a table in MS Access database using DAO.

I used the next method in SMExport suite:

1. declare the variables:
  access, db, td, recordset: Variant;

2. declare the array of consts with data type mappings
(between Delphi field types and DAO field types)

  arrMDBTypes: array[TFieldType] of Integer =
    ({dbText} 10 {ftUnknown},
     {dbText} 10 {ftString},
     {dbInteger} 3 {ftSmallint},
     {dbLong} 4 {ftInteger},
     {dbInteger} 3 {ftWord},
     {dbBoolean} 1 {ftBoolean},
     {dbDouble} 7 {ftFloat},
     {dbCurrency} 5 {ftCurrency},
     {dbDouble} 7 {ftBCD},
     {dbDate} 8 {ftDate},
     {dbTime} 22 {ftTime},
     {dbDate} 8 {ftDateTime},
     {dbLongBinary} 11 {ftBytes},
     {dbLongBinary} 11 {ftVarBytes},
     {dbInteger} 3 {ftAutoInc},
     {dbLongBinary} 11 {ftBlob},
     {dbMemo} 12 {ftMemo},
     {dbLongBinary} 11 {ftGraphic},
     {dbMemo} 12 {ftFmtMemo},
     {dbLongBinary} 11 {ftParadoxOle},
     {dbLongBinary} 11 {ftDBaseOle},
     {dbBinary} 9 {ftTypedBinary},
     {dbText} 10 {ftCursor}

    {$IFDEF VER120}
     {dbText} 10 {ftFixedChar},
     {dbText} 10 {ftWideString},
     {dbBigInt} 16 {ftLargeint},
     {dbText} 10 {ftADT},
     {dbText} 10 {ftArray},
     {dbText} 10 {ftReference},
     {dbText} 10 {ftDataSet}

    {$IFDEF VER125}
     {dbText} 10 {ftFixedChar},
     {dbText} 10 {ftWideString},
     {dbBigInt} 16 {ftLargeint},
     {dbText} 10 {ftADT},
     {dbText} 10 {ftArray},
     {dbText} 10 {ftReference},
     {dbText} 10 {ftDataSet}


    {$IFDEF VER130}
     {dbText} 10 {ftFixedChar},
     {dbText} 10 {ftWideString},
     {dbBigInt} 16 {ftLargeint},
     {dbText} 10 {ftADT},
     {dbText} 10 {ftArray},
     {dbText} 10 {ftReference},
     {dbText} 10 {ftDataSet},
     {dbLongBinary} 11 {ftOraBlob},
     {dbLongBinary} 11 {ftOraClob},
     {dbText} 10 {ftVariant},
     {dbText} 10 {ftInterface},
     {dbText} 10 {ftIDispatch},
     {dbGUID} 15 {ftGuid}


3. load a DAO:
      access := GetActiveOleObject('DAO.DBEngine.35');
      access := CreateOleObject('DAO.DBEngine.35');

4. open a database
      db := access.OpenDatabase(yourDatabaseName);

5. create a new table in opened database
    td := db.CreateTableDef(yourTableName, 0, '', '');

6. add a field descriptions in table
    td.Fields.Append(td.CreateField(strFieldName, arrMDBTypes[intDataType], Size));

for example,
    td.Fields.Append(td.CreateField('ID', arrMDBTypes[intDataType], Size));
    td.Fields.Append(td.CreateField('NAME', arrMDBTypes[intDataType], Size));

7. add a table definition in table list

8. open the created table in database
    recordset := db.OpenTable(yourTableName, 0);

9. append the new record in opened table

10. change the field values
     curField := recordset.Fields[0].Value := 1;
     curField := recordset.Fields[1].Value := 'First record';

11. post the new record
     recordset.Update(dbUpdateRegular, False);
  dbUpdateRegular = 1;

12. close a recordset

13. close a database

14. free a DAO instance
     access := UnAssigned;

For example, you can call this code in some cycle. For some task is very useful.

Published: January 3, 2001

See also
Clarion to Text converter
Paradox to MS Access converter
ExcelFile Viewer
Database Information Manager
Paradox Password Recovery
Mail parser (ActiveX)
DBISAM Password Recovery
Fast Document Viewer
Protected Storage Viewer

Contact to webmaster


Borland Software Code Gear Scalabium Delphi tips

Copyright© 1998-2022, Scalabium Software. All rights reserved.

SMReport Autogenerated