Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SQL query to change Required property MS Access
- Thread starter AvatarZA
- Start date
The 'Required' flag is equivalent to declaring a Column as NOT NULL using the Data Definition Language of SQL. That is NULL values will not be accepted.
As far as I know you can't change a NULLable column to NOT NULL once it has been defined without dropping the table and recreating it. This is because any existing data in the table may break this constraint.
When you change a column to Required in the Access table design mode it warns that it will verify the existing data and actually drops the table and recreates it if necessary.
So that's the solution for you as well. You'll need to check for null values in the column and replace them with something else such as an empty string or a zero, drop the table and finally recreate the table with the column redefined. Don't forget to reinstate all the indexes and foreign keys etc for the table as well.
As far as I know you can't change a NULLable column to NOT NULL once it has been defined without dropping the table and recreating it. This is because any existing data in the table may break this constraint.
When you change a column to Required in the Access table design mode it warns that it will verify the existing data and actually drops the table and recreates it if necessary.
So that's the solution for you as well. You'll need to check for null values in the column and replace them with something else such as an empty string or a zero, drop the table and finally recreate the table with the column redefined. Don't forget to reinstate all the indexes and foreign keys etc for the table as well.
- Thread starter
- #3
- Status
