Specifying Field Properties

The previous examples show a simple case of building a table with a single field by using default property values. Usually, you would specify many additional properties and override certain default values.

This example, which uses DAO, creates a table with four fields and specifies various nondefault values for the fields (ID, Name, Response, and Class), where strDbPath is the path to the database:

Dim dbs As Database, tdf As TableDef
Dim fldID As Field, fldName As Field
Dim fldResponse As Field, fldClass As Field

Set dbs = OpenDatabase(strDbPath)
Set tdf = dbs.CreateTableDef("MarketingSurvey")

' Create automatically incrementing field.
Set fldID = tdf.CreateField("ID", dbLong)
fldID.Attributes = dbAutoIncrField
fldID.Required = True

' Create text field.
Set fldName = tdf.CreateField("Name", dbText)
With fldName
	.Required = True
	.Size = 40
	.AllowZeroLength = True
	.DefaultValue = "Unknown"
End With

' Create memo field.
Set fldResponse = tdf.CreateField("Response", dbMemo)

' Create text field with validation rule.
Set fldClass = tdf.CreateField("Class", dbText, 10)
With fldClass
	.Required = True
	.ValidationRule = "In('A','B','X')"
	.ValidationText = "Enter one of A, B, or X."
End With

With tdf
	.Fields.Append fldID
	.Fields.Append fldName
	.Fields.Append fldResponse
	.Fields.Append fldClass
End With
dbs.TableDefs.Append tdf

When you use Microsoft Jet SQL DDL statements, you can specify only the field names and data types. Microsoft Jet SQL doesn’t support ANSI SQL constructs that set validation rules or default values (for example, field-level CHECK or DEFAULT clauses). You can’t use SQL DDL to fill in the ValidationRule and DefaultValue properties of a field, for example. The closest you can come to re-creating the previous table by using only SQL DDL statements is:

CREATE TABLE MarketingSurvey
	(ID SHORT,
	Name TEXT (40),
	Response MEMO,
	Class TEXT (10));