Making Structural Changes by Using DAO

Using DAO to make table modifications gives you much more flexibility than using SQL DDL statements.

Deleting a table by using DAO requires you to delete the TableDef object from the database’s TableDefs collection. In the following example, dbs is a Database object and strTableName is the name of the table to delete:

dbs.TableDefs.Delete strTableName

The following code creates a new field and specifies it as the second field in the table (ordinal position of 1, with numbering beginning at 0). In this example, strDbPath is the path to the database, strTableName is the name of the table, strFieldName is the name of the field, and intType is a constant specifying what type of field to create:

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

Set dbs = OpenDatabase(strDbPath)
Set tdf = dbs.TableDefs(strTableName)
Set fld = tdf.CreateField(strFieldName, intType)
fld.OrdinalPosition = 1
tdf.Fields.Append fld

The equivalent code to delete a field from a table is:

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

Set dbs = OpenDatabase(strDbPath)
Set tdf = dbs.TableDefs(strTableName)
tdf.Fields.Delete strFieldName