William, the simplest way to achieve this is to use Access 97, where the
Allow Zero Length (AZL) property always defaults to No, the value you should
use, so there is no confusion trying to differentiate between Null and a
Zero-Length String (ZLS).
Believe me, users cannot tell the difference, many developers cannot tell
the difference, and Access itself cannot the difference correctly, e.g.
DLookup() wrongly returns Null for a field that contains a ZLS.
The later versions are inconsistent in the setting of the property,
depending whether the table was created via the interface, a DLL query
statement, DAO, or ADOX. Can't remember the results, but if you experiement
with the 4 approaches, you will find some of them have AZL turned on.
If you actually want to control the AZL property, you must use DAO. The code
looks like this:
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb()
Set tdf = db.TableDefs("MyTable")
For Each fld In tdf.Fields
Select Case fld.Type
Case dbText, dbMemo
fld.AllowZeroLength = False
End Select
Next
Set fld = Nothing
Set tdf = Nothing
Set db =Nothing
