1

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...

Tracer
  • 2,544
  • 2
  • 23
  • 58

3 Answers3

4

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
harrybvp
  • 2,445
  • 2
  • 22
  • 30
  • It allows me to specify null in alter table, but it has no effect and that is my problem. I saw your solution in some other places but I was hoping I would not need to do that this way.. Thanks anyway. – Tracer Aug 10 '13 at 12:15
0

Try something like this using MODIFY :-

ALTER TABLE Table1 MODIFY myColumn INT NULL;
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
0

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

BMcDowell
  • 1