"Default Value" Access

[barski]

i don't work with access very much so i'm having a little trouble with this one.

 

how do i set the default value with alter statements. i have this which gets me half the way there

 

ALTER TABLE TESTTABLE ADD COLUMN MYFIELD INTEGER  NOT NULL

 

i have tried putting Default 0, Default(0) , Default=0, and numerous other combinations at the end of the above statement but know default value of zero is set.

[Shurik12]

You current statement should be working (well at least if you run in the Access environment itself)

 

What's your code so far?

 

Shurik.

[barski]

it does work but i would like to set the default which is not happening.

 

i've tried several combinations of syntax to set it but no success. i really think it's one of those thing it should do but doesn't.

[MrPaul]

Possible Access quirk

 

The correct syntax would be:

 

ALTER TABLE TESTTABLE ADD COLUMN MYFIELD INTEGER NOT NULL DEFAULT 0;

 

If, as you say, this causes the column to be added but without the default value set, then Access is not behaving as it should.

 

However, I'd be interested to see if this affects setting any default value - does it work if you use another number (non-zero) as the default? Also, does it work if you add the column and then set the defaut value in a seperate SQL statement?

[Nerseus]

Because I, personally, don't like adding defaults to a column when I don't really want one, here's an approach to add the column, set the default, and change the column:

ALTER TABLE TESTTABLE ADD MYFIELD INTEGER  NULL
GO
UPDATE TESTTABLE SET MYFIELD = 0
GO
ALTER TABLE TESTTABLE ALTER COLUMN MYFIELD INTEGER  NOT NULL

 

In my test against SQL Server (not Access), the first ALTER should NOT have the word "COLUMN" - just "...ADD MYFIELD...". The second alter needs the words "...ALTER COLUMN...". Mabye Access syntax is different?

 

-nerseus