Scalabium Software

SMExport advertising
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
Paradox to Text converter
Paradox Password Recovery
ABA Database Convert
Mail parser (ActiveX)
Paradox ActiveX
MAPIMail
SMMsg suite
Database Information Manager
Metafile Convert
 
 


Contact to webmaster

 

Borland Software Code Gear Scalabium Delphi tips

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

SMReport Autogenerated