Create the MS Access table in run-time

Problem/Question/Abstract:

How can I create a table in MS Access database from own application?

Answer:

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

1. declare the variables:

var
  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},
  {dbDate} 8 {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}
{$ELSE}
{$IFDEF VER125}
  ,
  {dbText} 10 {ftFixedChar},
  {dbText} 10 {ftWideString},
  {dbBigInt} 16 {ftLargeint},
  {dbText} 10 {ftADT},
  {dbText} 10 {ftArray},
  {dbText} 10 {ftReference},
  {dbText} 10 {ftDataSet}

{$ELSE}
{$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}
{$ENDIF}
{$ENDIF}
{$ENDIF}
  );

3. load a DAO:

try
  access := GetActiveOleObject('DAO.DBEngine.35');
except
  access := CreateOleObject('DAO.DBEngine.35');
end;

4. open a database

try
  db := access.OpenDatabase(yourDatabaseName);
except
  exit
end;

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

db.TableDefs.Append(td);

8. open the created table in database

recordset := db.OpenTable(yourTableName, 0);

9. append the new record in opened table

recordset.AddNew;

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);

where

const
  dbUpdateRegular = 1;

12. close a recordset

13. close a database

db.Close;

14. destroy a DAO

access := UnAssigned;