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]

You could do it without the reference using late-binding.

In fact it might be worth doing that if this is going to be distributed in some way.

It could be possible that some machines/people have a different version of the DAO library.

If they do then the reference might break.

Perhaps unlikely but it shouldn't be too hard to change what you have to late-binding.

Actually, when I look at the code I think all you need to do is change both DAO.TableDef and DAO.Field to Object.

 

[PaulZak]

Interesting idea. I think I understand the concept of early- or late-binding but I don't quite know how to do one vs. the other in code form so I'd need to study this much more before trying.

But I could definitely try your final bit of advice. That's clear enough. I'll keep that in mind. Thanks.

 

[Norie]

Well that's all you should need to do for that small piece of code - it really is that simple in this case.