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"

rsdata.Open szSQL, gDBconnect, adOpenStatic, adLockOptimistic, adCmdText

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?

Thanks in advance.

Here's the solution that worked for me using DAO.
I ran this VBA from module subprocedure in Excel.

(IMPORTANT: This requires adding to Excel file reference:
"Microsoft DAO x Object Library")

Dim tdf1 As DAO.TableDef
Dim fld As DAO.Field
Dim acc As Object
Dim db1 As Object
'_________________________________________________ ______
Set acc = CreateObject("Access.Application")

acc.visible = False

'(Password parameter is optional)
Set db1 = acc.DBEngine.OpenDatabase(gMDBName, False, False, ";PWD=" & gStdPassword)

acc.OpenCurrentDatabase gMDBName

Set tdf1 = db1.TableDefs("xxxxx")

Set fld = tdf1.Fields("ffffff")
fld.Required = False

db1.Close

Set db1 = Nothing