I think a good solution to the following problem might be useful to many folks out there.
I have a table with several existing fields that previously we have required to have values. Now, we want to remove that requirement in each user's db file. (In the mdb file, the fields "Required" property is True.) I want to make that property false by running VBA from our Excel (2007) front end. In the past, I've used ALTER TABLE (SQL) to make a field required but can't seem to remove that requirement now, for some reason.
These fields are not part of a key. I don't see any related constraint, either. They are not indexed. They are simply text fields. As a test I've added a new field to the table (making it required using NOT NULL) but then could not remove the requirement using ALTER TABLE.
Set rsdata = New ADODB.Recordset
szSQL = "ALTER TABLE [strTableName] ALTER COLUMN [strFieldName] Text(50) NULL"
This codes runs and even changes the field length but it does not change the "Required" property. I've also run a version of this code using EXECUTE and got the same results. Only field length was changed. I can manually open my cope of the mdb file and set the fields Required = false but I need to do this with code for multiple users/files. That is the problem.
I'm finding numerous posts about making a field required, but nothing really about removing/changing the requirement. Can someone please help with this?