0

I'm trying to add a column of the type NUMBER to my table using VBA. That is working for me. But i also want to set the default value for the column to 0, which is not working properly. I believe i need some help.

I do not want to alter a column, i want to add a column and at the same time set a default value.

The following code is working, it adds a column with nothing in it.

strSQL = "ALTER TABLE testTable ADD COLUMN testColumn NUMBER;"
dbs.Execute strSQL

But this is not working and gives an error.

strSQL = "ALTER TABLE testTable ADD COLUMN testColumn NUMBER DEFAULT 0;"
dbs.Execute strSQL

The error message i get is "Run-time error'3293': Syntax error in ALTER TABLE statement."

braX
  • 11,506
  • 5
  • 20
  • 33
Tesel
  • 41
  • 9
  • 1
    Possible duplicate of [SQL SET DEFAULT not working in MS Access](https://stackoverflow.com/questions/14057085/sql-set-default-not-working-in-ms-access) – Vincent G Aug 19 '19 at 13:32

1 Answers1

2

Default only works when it's used in DDL executed from an ADO connection. The syntax for this, which I have tested would be this:

CurrentProject.Connection.Execute "ALTER TABLE testTable ADD COLUMN testColumn NUMBER"
CurrentProject.Connection.Execute "ALTER TABLE testTable ALTER COLUMN testColumn SET DEFAULT 0"

Also, this only sets the default for the table for all new records, and does not modify existing records to 0. If that is also your intention, simply run an update query, like this one.

strSQL = "UPDATE testTable SET testTable.testColumn = 0;"
CurrentDb.Execute strSQL
Gonzalo Garcia
  • 6,192
  • 2
  • 29
  • 32
Dawn Rose
  • 61
  • 1
  • 5