Set Def Value

[Darren]

How can I set the default value of a field in Access SQL

 

[Allen Browne]

I don't think you can do that through in the query window, because Microsoft
did not upgrade it to work with the new features in Jet 4.

With Access 2000 and later, you can execute a DDL query with ADO and set the
default value to a literal value (not an expression AFAIK). In this example,
the HourlyFee field gets a default value of zero:

Dim cmd As New ADODB.Command
Dim strSql As String

cmd.ActiveConnection = CurrentProject.AccessConnection
strSql = "CREATE TABLE MyTable " & _
"(MyID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"Surname TEXT(30) WITH COMP NOT NULL, " & _
"FirstName TEXT(20) WITH COMP, " & _
"HourlyFee CURRENCY DEFAULT 0 );"
cmd.CommandText = strSql
cmd.Execute
Debug.Print "tblDdlContractor created."