Altering Field and Table Properties by Using DAO

You can modify the following field and table properties after the table is created.

Property Applies to this object
AllowZeroLength Field
Attributes TableDef and Field
DefaultValue Field
OrdinalPosition Field
Name TableDef and Field
ValidateOnSet Field, only when accessed with the Recordset object
ValidationRule TableDef and Field

(For an example of adding a table-level validation rule to an existing table, see the “Table-Level Validation” section earlier in this chapter.)

ValidationText TableDef and Field
Value Field, only when accessed with the Recordset object after using the AddNew or Edit method

Unlike SQL DDL, which gives you no way to change a field’s name, you can use DAO to change the Field object’s Name property. In the following example, strDbPath is the path to the database, strTableName is the name of the table, strFieldName is the existing name for the field, and strNewFieldName is the new name for the field:

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

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

Properties other than those listed in the table above can’t be changed. For example, the following code fails because you can’t change the Type property of a field once it has been created:

Set tdf = dbs.TableDefs(strTableName)
tdf.Fields(strFieldName).Type = dbInteger '<<< Error occurs here.