Scalabium Software

SMReport Autogenerated
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
 
DBISAM Viewer
SMDBGrid
DBISAM Password Recovery
MAPIMail
SMMsg suite
SMReport
SMImport suite
ABA Database Convert
Word Web-stream
ExcelFile Viewer
 
 


Contact to webmaster

 

Borland Software Code Gear Scalabium Delphi tips

Copyright© 1998-2017, Scalabium Software. All rights reserved.
webmaster@scalabium.com

SMExport advertising