Databases Reference
In-Depth Information
Set tblNew = db.CreateTableDef("NewTable")
Set fld = tblNew.CreateField("NewField", dbText, 100)
' Set properties of field BEFORE appending
' zero length value is OK
fld.AllowZeroLength = True
' default value is 'Unknown'
fld.DefaultValue = "Unknown"
' Null value not allowed
fld.Required = True
' Validation
fld.ValidationRule = "Like 'A*' or Like 'Unknown'"
fld.ValidationText = "Known value must begin with A"
' Append field to Fields collection
tblNew.Fields.Append fld
' Append table to TableDef collection
db.TableDefs.Append tblNew
End
Sub
Setting the validation properties of a field requires setting two properties. The
ValidationRule property is a text string that describes the rule for validation, and the
ValidationText is a string that is displayed to the user when validation fails. After running
the code from Example 15-2, a new table appears in the Access Database window. (You
may need to move away from the
Tables
tab and then return to that tab to see the new
table.) Opening this table in Design View shows the window in Figure 15-1. Note that the
Field Properties
setting reflects the properties set in our code.
Figure 15-1. Design view of table generated from running exaCreateTable
Incidentally,
TableDef
objects also have ValidationRule and ValidationText properties,
used to set validation rules that involve multiple fields in the table.
