SQL DEFAULT values in Access (1 Viewer)

[mickeyblue]

I am currently attempting to use SQL in Access to alter a table by adding a new attribute and giving it a default value. Creating the attribute is fine but i keep getting an error when attempting to assign a default value. My code is as follows:

ALTER TABLE Objects
ADD Status Text DEFAULT 'Object is Currently In';

It keeps telling me that I have a Syntax error.

Any help would be greatly appreciated.

Thanks

 

[Graham T]

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

Taken from Intermediate Microsoft Jet SQL for Access 2000

HTH

Graham

 

[mickeyblue]

Thanks Graham.

Does anyone know of a way to create default values using the standard SQL interface?

 

[Graham T]

mickeyblue

If you add the following to a button on your form, named cmdAddColumn, it should alter the table:

Private Sub cmdAddColumn_Click()
    Dim conDatabase As ADODB.Connection
    Dim SQL As String

    On Error GoTo Error_Handler
    Set conDatabase = Application.CurrentProject.Connection

    SQL = "ALTER TABLE Objects ADD COLUMN Status TEXT(100) DEFAULT Object is Currently In"


    conDatabase.Execute SQL
    MsgBox "The new column has been created!"
    conDatabase.Close
    Set conDatabase = Nothing
    Exit Sub
Error_Handler:
    MsgBox Err.Description, vbInformation
End Sub

Graham