|
|
A default value allows a column to use a value that is
supposed to be common to most cells of a particular column. The default
value can be set as a constant value or it can use a function that would
adapt to the time the value is needed.
|
To sepecify the default value of a column, after its
name and its data type, type DEFAULT followed by the
desired default value. If the column is a numeric type, provide its value
as the number. If the field is character or string-based, include its
value in single-quotes. Here is an example:
Private Sub cmdCreateTable_Click()
Dim conDatabase As ADODB.Connection
Set conDatabase = CurrentProject.AccessConnection
conDatabase.Execute "CREATE TABLE Employees" & _
"(" & _
" EmplNumber TEXT(6)," & _
" HourlySalary Currency default 12.50," & _
" FirstName Text(20)," & _
" LastName Text(20)," & _
" Address varchar(100)," & _
" City VARCHAR(40)," & _
" State char(2)," & _
" ZIPCode varchar(20)," & _
" Country varchar(50) DEFAULT 'USA'" & _
");"
End Sub
Although the SQL as a language supports default
values, Microsoft Access SQL does not allow a default value in a statement
executed by the DoCmd object. If you try, you would
receive an error. For example, the following code will produce a 3290
error (Syntax Error in CREATE TABLE Statement):
Private Sub cmdCreateTable_Click()
DoCmd.RunSQL "CREATE TABLE Employees" & _
"(" & _
" EmplNumber TEXT(6)," & _
" HourlySalary Currency default 12.50," & _
" FirstName Text(20)," & _
" LastName Text(20)," & _
" Address varchar(100)," & _
" City VARCHAR(40)," & _
" State char(2)," & _
" ZIPCode varchar(20)," & _
" Country varchar(50) DEFAULT 'USA'" & _
");"
End Sub
