"Default Value" Access

B

barski

Well-known member
Joined
Apr 7, 2002
Messages
239
Location
Tennessee
i dont work with access very much so im 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

Code: 
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.
 
You current statement should be working (well at least if you run in the Access environment itself)

Whats your code so far?

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

ive tried several combinations of syntax to set it but no success. i really think its one of those thing it should do but doesnt.
 
Possible Access quirk

The correct syntax would be:

Code: 
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, Id 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?
 
Because I, personally, dont like adding defaults to a column when I dont really want one, heres an approach to add the column, set the default, and change the column:
Code: 
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
 
Back
Top