Microsoft Access Database Development With VBA

SQL Keywords: DEFAULT

   

Description

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
 
 
     
 

Home Copyright © 2013 FunctionX, Inc. Home