Author: Mike Shkolnik

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: 
1   
2   var
3     access, db, td, recordset: Variant;


2. declare the array of consts with data type mappings (between Delphi field types 
and DAO field types) 
4   
5     arrMDBTypes: array[TFieldType] of Integer =
6   ({dbText} 10 {ftUnknown},
7     {dbText} 10 {ftString},
8     {dbInteger} 3 {ftSmallint},
9     {dbLong} 4 {ftInteger},
10    {dbInteger} 3 {ftWord},
11    {dbBoolean} 1 {ftBoolean},
12    {dbDouble} 7 {ftFloat},
13    {dbCurrency} 5 {ftCurrency},
14    {dbDouble} 7 {ftBCD},
15    {dbDate} 8 {ftDate},
16    {dbDate} 8 {ftTime},
17    {dbDate} 8 {ftDateTime},
18    {dbLongBinary} 11 {ftBytes},
19    {dbLongBinary} 11 {ftVarBytes},
20    {dbInteger} 3 {ftAutoInc},
21    {dbLongBinary} 11 {ftBlob},
22    {dbMemo} 12 {ftMemo},
23    {dbLongBinary} 11 {ftGraphic},
24    {dbMemo} 12 {ftFmtMemo},
25    {dbLongBinary} 11 {ftParadoxOle},
26    {dbLongBinary} 11 {ftDBaseOle},
27    {dbBinary} 9 {ftTypedBinary},
28    {dbText} 10 {ftCursor}
29  {$IFDEF VER120}
30    ,
31    {dbText} 10 {ftFixedChar},
32    {dbText} 10 {ftWideString},
33    {dbBigInt} 16 {ftLargeint},
34    {dbText} 10 {ftADT},
35    {dbText} 10 {ftArray},
36    {dbText} 10 {ftReference},
37    {dbText} 10 {ftDataSet}
38  {$ELSE}
39  {$IFDEF VER125}
40    ,
41    {dbText} 10 {ftFixedChar},
42    {dbText} 10 {ftWideString},
43    {dbBigInt} 16 {ftLargeint},
44    {dbText} 10 {ftADT},
45    {dbText} 10 {ftArray},
46    {dbText} 10 {ftReference},
47    {dbText} 10 {ftDataSet}
48  
49  {$ELSE}
50  {$IFDEF VER130}
51    ,
52    {dbText} 10 {ftFixedChar},
53    {dbText} 10 {ftWideString},
54    {dbBigInt} 16 {ftLargeint},
55    {dbText} 10 {ftADT},
56    {dbText} 10 {ftArray},
57    {dbText} 10 {ftReference},
58    {dbText} 10 {ftDataSet},
59    {dbLongBinary} 11 {ftOraBlob},
60    {dbLongBinary} 11 {ftOraClob},
61    {dbText} 10 {ftVariant},
62    {dbText} 10 {ftInterface},
63    {dbText} 10 {ftIDispatch},
64    {dbGUID} 15 {ftGuid}
65  {$ENDIF}
66  {$ENDIF}
67  {$ENDIF}
68    );


3. load a DAO: 
69  
70  try
71    access := GetActiveOleObject('DAO.DBEngine.35');
72  except
73    access := CreateOleObject('DAO.DBEngine.35');
74  end;
75  
76  4. open a database 
77  
78  try
79    db := access.OpenDatabase(yourDatabaseName);
80  except
81    exit
82  end;


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

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


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


7. add a table definition in table list 
91  
92  db.TableDefs.Append(td);


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


9. append the new record in opened table 

recordset.AddNew;

10. change the field values 
95  
96  curField := recordset.Fields[0].Value := 1;
97  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;