Solution
Pat:
In the future, for those others searching behind, could you give a little more information? If it is a simple syntax question, like how can I change the type for a column, you could tell him to check the help file but also give him:
alter table MyTable alter column MyColumn NewType;
However, in this particular case, it is not just a simple SQL statement in Access, to just lookup the syntax (unless I am just totally confused). I was trying to do this (remove the REQUIRED restriction), and it seems that there are several steps you need to do.
Access has a limited DDL syntax, and does not provide a statement to change the NULL / NOT NULL restriction. Therefore, you must create a new column to hold the old data, drop the existing column, and then recreate it, and copy the data back.
In this example, the table name is MyTable, and I want to remove the "NOT NULL" restriction from the Field1 column, and is a Long field (I hesitate to call NOT NULL a constraint, since constraints are a separate SQL concept).
1) alter table MyTable add column NewField1 long;
Note the lack of "NOT NULL" (aka required) restriction above...
2) update table MyTable set NewField1 = Field1;
3) alter table MyTable drop column Field1;
4) alter table MyTable add column Field1 long;
Again, I removed the NOT NULL (aka required) restriction
5) update table MyTable set Field1 = NewField1;
You could similarly add a NOT NULL restriction, but you would want to make sure your data you were copying did not have NULLs. You could add a step 2b, like this:
2b) update table MyTable set NewField1 = 0 where NewField1 is null;
Hope that helps you, mrpon, if you are still listening!
~Prosthetic.Lips
