Allow Zero Length

[George]

Is is possible to make an empty copy of a table, and then
programmatically set Allow Zero Length to Yes, and
Required to No for all (or non-key) fields?

I can re-build the table programmatically, but I cannot
figure out how to change these properties.

Thank you for any help you can give.
I need to do this sort of thing ALL THE TIME, and
currently, I change each field using Table Design View.

George

 

[Allen Browne]

The function below shows how to loop through all tables (except the system
ones), and set the AllowZeroLength property. You could set the Required
property the same way.

Personally, I allow zero-length-strings (ZLS) in text fields only in
extremely rare cases. Since there is no visible difference to the user, the
distinction is confusing to them. It's more work to have to handle both Null
and ZLS. If your goal was to avoid Nulls, it's not going to work: no matter
what you store in your table, you'll still have Nulls, e.g. in the fields of
a related table in a query with outer joins. You also have to workaround the
fact that Access itself does not handle the distinction well, e.g. DLookup()
falsely returns Null if the lookup field contains a ZLS.

Function FixZLS()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Const conPropName = "AllowZeroLength"
Const conPropValue = False

Set db = DBEngine(0)(0)
For Each tdf In db.TableDefs
If (tdf.Attributes And dbSystemObject) = 0 Then
Debug.Print tdf.Name
For Each fld In tdf.Fields
If fld.Properties(conPropName) Then
Debug.Print tdf.Name & "." & fld.Name
fld.Properties(conPropName) = False
End If
Next
End If
Next

Set prp = Nothing
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

 

[George]

That is extremely helpful.
Thank you very much.

I rarely use constants. I assume I can use the literal.
Also, I'm going to try it in ADO. Do you think that will
work?

George