Scalabium Software
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:
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},
     {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}
    {$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
     recordset.Close;

13. close a database
     db.Close;

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

	Database Information Manager
	dBase Viewer
	Clarion Viewer
	Fast Document Viewer
	DBISAM Viewer
	MAPIMail
	SMReport
	DBExport tools
	Excel Reader (dll)
	Viewer for MS Outlook Messages

Contact to webmaster