allow zero length

[vicky]

Hi all,
i want to add a new field in a existing table of access using alter table
query and i want that field to allow zero length. What will be the syntax of
the query.
I do not want ot use adox please

its urgent

Thanks

 

[Allen Browne]

CurrentDb().TableDefs("MyTable").Fields("MyField").Properties("AllowZeroLeng
th") = True

Unless you need it for compatibility with other external databases, the
AllowZeroLength property is a pain. The cases where it is useful are
extremely limited. You have to test for both zero-length and null. The
difference is too subtle for your users. You still have to handle nulls
(e.g. in outer join queries). It can make debugging more difficult (e.g.
foreign keys that contain a zls). And Access itself is inconsistent in its
implementation, e.g. DLookup() wrongly returns Null for a zls, and the
actual setting varies with the version of Access and how the field was
created.

As a result, we always run this code to tunr off AllowZeroLength for *all*
fields in the database except system tables:

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