Like you, Anne, I really wish there was some way to turn AZL off by default.
It used to be off in earlier versions of Access, and I can't think of any
good reason why it should be on by default. That's even more so, because
Access itself does not handle the distinction between a zero-length-string
and a Null correctly: DLookup wrongly returns Null when it looks up a field
that contains a ZLS.
The best I can offer you is a piece of code that visits all the non-system
tables in your database, and turns ZLS off. Trouble is that you have to run
the code again every time you add tables or fields. Hope it's some
consolation:
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
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
