Creating Tables by Using DAO

The DAO equivalent for the SQL DDL statement CREATE TABLE MyTable (MyField TEXT); is as follows, where strDbPath is the path to the database:

Dim dbs As Database, tdf As TableDef
Dim fld As Field

Set dbs = OpenDatabase(strDbPath)

' Create new TableDef object.
Set tdf = dbs.CreateTableDef("MyTable")

' Create new Field object.
Set fld = tdf.CreateField("MyField", dbText)
tdf.Fields.Append fld
dbs.TableDefs.Append tdf

The DAO method and the SQL DDL method create identical tables. The method you use depends on what you want to do, and the style of programming you prefer. In most cases, DAO is more powerful and flexible than SQL DDL.

The DAO techniques used to create and modify tables are similar to the way Microsoft Jet uses hierarchical collections in other contexts. For example, you can append a Field object to the Fields collection by using DAO in ways similar to those used to add a user-defined property to a form in a Microsoft Access application. In addition, certain types of Microsoft Access-specific properties, such as the ValidationRule and DefaultValue properties of fields, can be set only through the Microsoft Access user interface or through DAO in code.