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