You're fairly limited with what you can do in DDL.
AFAIK, you can set the Default Value of a column to a literal value only,
not to a function such as =Date(). Further, some of these properties work
only if you execute your DDL query under ADO, i.e. they don't work if you
execute under DAO and therefore they do not work if you try them in the
query interface. Other properties (such as Format) can be set only through
DAO code.
This example shows how to create a variety of field types, and set:
- Default Value,
- Unicode Compression,
- Required,
- a primary key index,
- a unique multi-field index.
Sub CreateTableDDL()
Dim cmd As New ADODB.Command
Dim strSql As String
cmd.ActiveConnection = CurrentProject.AccessConnection
'Create the Contractor table.
strSql = "CREATE TABLE tblDdlContractor " & _
"(ContractorID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"Surname TEXT(30) WITH COMP NOT NULL, " & _
"FirstName TEXT(20) WITH COMP, " & _
"Inactive YESNO, " & _
"HourlyFee CURRENCY DEFAULT 0, " & _
"PenaltyRate DOUBLE, " & _
"BirthDate DATE, " & _
"Notes MEMO, " & _
"CONSTRAINT FullName UNIQUE (Surname, FirstName));"
cmd.CommandText = strSql
cmd.Execute
End Sub
