Insert default values in a column (access db)

Question

How can I do for inserting a Default Value into a column of a table in Access? I use this instruction:

ALTER TABLE Tabella ADD Campo Double DEFAULT 0
ALTER TABLE Tabella ADD Campo Double DEFAULT (0)
ALTER TABLE Tabella ADD Campo DEFAULT 0
ALTER TABLE Tabella ADD Campo DEFAULT (0)
ALTER TABLE Tabella ADD Campo SET DEFAULT 0
ALTER TABLE Tabella ADD Campo SET DEFAULT (0)

but all of these cause error. How can I do for doing it?

Answer 1

See this answer?

SQL to add column with default value - Access 2003

Answer 2

From MSDN:

The DEFAULT statement can be executed only through the Access OLE DB provider and ADO. It will return an error message if used through the Access SQL View user interface.

Sooo... tell us more about what you're doing and how you're executing your SQL?

And why aren't you using the table designer?

Answer 3

You do not insert a new default value, you need to alter the existing one.

First switch Access to a sane SQL flavour, as in the answer linked above, then you can say:

alter table Tabella alter column Campo Double DEFAULT 0

Answer 4

I have this typical Access function to add new fields + default values to Access tables:. The field is added if it does not already exists in the table.

Public Function addNewField( _
    m_tableName as string, _
    m_fieldName As String, _
    m_fieldType As Long, _      'check syntax of .createField method for values'
    m_fieldLength As Long, _    'check syntax of .createField method for values'
    Optional m_defaultValue As Variant = Null)

Dim myTable As DAO.TableDef
Dim myField As DAO.Field

On Error GoTo addNewField_error

Set myTable = currentDb.TableDefs(m_tableName)
Set myField = myTable.CreateField(m_fieldName, m_fieldType, m_fieldLength)

If Not IsNull(m_defaultValue) Then
    myField.DefaultValue = m_defaultValue
End If

myTable.Fields.Append myField

Set myTable = Nothing
Set myField = Nothing

Exit Function

addNewField_error:
If Err.Number = 3191 Or Err.Number = 3211 Then
    'The field already exists or the table is opened'
    'nothing to do but exit the function'
Else
    debug.print Err.Number & " - " & Error$
End If

End Function