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

	SMDBGrid
	ABA Picture Convert
	Excel Reader (dll)
	Protected Storage Viewer
	Paradox ActiveX
	DBLoad
	Viewer for MS Outlook Messages
	ABA Document Convert
	SMMsg suite
	dBase Viewer

Contact to webmaster