SQL Alter Table - Remove NOT NULL

[PaulZak]

I have a table with several existing fields that we have required to have values in the past. Now, we want to remove that requirement. (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 do the reverse 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 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

I'm finding only posts about making a field required, not removing the requirement. I can manually open mdb file and set the fields Required = false but I need to do this with code for multiple users/files. That is the problem.

Can someone please help with this?
Thanks in advance.

 

[Norie]

Are you sure the SQL is being executed?

Perhaps you could try using Execute instead of Open?

 

[PaulZak]

Yes, I think it's executing because I can run ALTER to change an existing field's size (say from 55 to 50, as shown in example code) successfully but the "Required" property won't convert to false despite having "Null" in SQL.

And as mentioned, I can also test by running ALTER successfully to add a temporary test field to this table. I can add it as required or I can add it not required...all just fine. But then I can't get the ALTER to change that field's "Required" property from true to false, just like I can't do this for the existing fields.

Can you actually change a field's Required property from true to false on your system?

 

[PaulZak]

I also tried using EXECUTE, as suggested above, but still no change (and no error).

As before, the ALTER statement changes the field length but *not* the required property to false.

 

[Norie]

Paul

I think this might not be possible check this DDL Code Examples.

 

[PaulZak]

Thanks for the link. So I guess you're referring to the page's text. It just seems odd that I can set the Required property to be true using NOT NULL but then can't set that property to be false. Well, thanks for the reply.


"While DDL is important in some larger databases, it is of limited use in Access. You can create a Text field, but you cannot set the Allow Zero Length property to No, so any query criteria must test for both Nulls and zero-length strings. You can create a Yes/No field, but you get a text box, not a check box, since you cannot set the Display Control property with DDL. You can create a Date/Time field, but you cannot set the Format property. DDL cannot create Hyperlink fields, Attachment fields, or the complex data types at all."

 

[Norie]

Perhaps it's possible using some other method.

Or you could take drastic measures and add a new field with identical properties, except the required bit, use an UPDATE query to populate that and delete
the original field.

That should work but like I said perhaps a bit drastic.

 

[PaulZak]

Here's the solution that worked for me (running 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

 

[Norie]

That was one of the methods I was thinking of, but my DAO's a bit rusty.

I actually tried doing something similar with ADOX catalog but it didn't work of course.

 

[PaulZak]

Thanks again for your input. I'm never eager to add another reference to my Excel project file because it could prove problematic on some user's machines. So I hesitated to do this. But it seems the only way.

I hope this post will prove useful to others also attempting this task.