MS Access - sql expression for allow null?

问题:

I use MS Access (2003) database. Once I create a column I set NOT NULL using sql statement:

ALTER TABLE Table1 
ALTER column myColumn INTEGER not null      

Is there a way to change it back to allow null values? I already tried:

ALTER TABLE Table1 
ALTER column myColumn INTEGER null      

but nothing...

回答1:

You cant specify null in ALTER TABLE (although not null is allowed)

See the below documentation and also this discussion on this toppic

Syntax

ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL]     [CONSTRAINT index] |     ALTER COLUMN field type[(size)] |     CONSTRAINT multifieldindex} |     DROP {COLUMN field I CONSTRAINT indexname} }

Old School Solution:-

• create a new temporray field as null with the same datatype
• update the new temporary field to the existing NOT NULL field
• drop the old NOT NULL field
• create the droped column with the same datatype again without NOT NULL
• update the existing field to the temporary field
• if there have been indices on the existing field, recreate these
• drop the temporary field

回答2:

Try something like this using MODIFY :-

ALTER TABLE Table1 MODIFY myColumn INT NULL;

回答3:

The only way I've found is to use DAO directly on the table. Set db.TableDefs(strTable1).Fields(strFieldName).Required = false