How to set the default value while adding a new column

Question

I am using the following vba code to execute an sql statement in Access 2007:

SQL = "ALTER TABLE " & strNEW & _
      " ADD COLUMN ([GolfLengte] INT) DEFAULT " & _
      strTableName

DoCmd.RunSQL (SQL)

Wich prints:

ALTER TABLE tbl_GL_850 ADD COLUMN ([GolfLengte] INT) DEFAULT 850

The query should update an already existing table with already existing records:

    Table1
+--------------+----------+----------+
| Column1      |  Column2 |  Column3 |                  
+--------------+----------+----------+
|       x      |    x     |    x     | 
+--------------+----------+----------+
|       x      |    x     |    x     | 
+--------------+----------+----------+
|       x      |    x     |    x     |
+--------------+----------+----------+
|       x      |    x     |    x     |
+--------------+----------+----------+

What I would like is another column added with In every record the value that is written in the variable strTableName like so:

    Table1              strTableName = 850
+--------------+----------+----------+----------+
| Column1      |  Column2 |  Column3 |GolfLengte|         
+--------------+----------+----------+----------+
|       x      |    x     |    x     |   850    |
+--------------+----------+----------+----------+
|       x      |    x     |    x     |   850    |
+--------------+----------+----------+----------+
|       x      |    x     |    x     |   850    |
+--------------+----------+----------+----------+
|       x      |    x     |    x     |   850    |
+--------------+----------+----------+----------+

Seems to me that it is a valid SQL statement, but for some reason vba keeps telling me that there is a syntax error while highlighting

DoCmd.RunSQL (SQL)

What am I doing wrong?

LS_ᴅᴇᴠ · Accepted Answer · 2013-07-30 09:16:02Z

2

AFAIK, ALTER TABLE change table schema, but not data.

You must "UPDATE " & strNEW & " SET GolfLengte = " & strTableName after ALTER TABLE.

answered Jul 30, 2013 at 9:16

LS_ᴅᴇᴠ

11.2k1 gold badge27 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PaulG · Accepted Answer · 2013-07-30 07:51:29Z

0

try

ALTER TABLE tbl_GL_850
ADD GolfLengte INT NOT NULL DEFAULT(850)

apologies....this is SQL, I missed the VBA Access part of the question (it's early)

try:

ALTER TABLE tbl_GL_850 ADD COLUMN GolfLengte INT DEFAULT 850

edited Jul 30, 2013 at 7:51

answered Jul 30, 2013 at 7:33

PaulG

5922 silver badges8 bronze badges

2 Comments

Gutanoth Over a year ago

This gave me an error : Error 3320 while executing missing ),] or element in expression

Gutanoth Over a year ago

This doesnt work either, no error, but my "golfLengte" column stays empty

Collectives™ on Stack Overflow

How to set the default value while adding a new column

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related