Setting table property via code

[John]

Hi

Is it possible to set Allow Zero Length = Yes property for all relevant
fields automatically via code?

Thanks

Regards

 

[Marco Pagliero]

Is it possible to set Allow Zero Length = Yes property for all relevant
fields automatically via code?

This works in Access 97:

Sub testAllow()
Dim dbs As Database, tdf As TableDef
Dim fld As Field
Set dbs = CurrentDb
Set tdf = dbs.TableDefs!YourTable

'Fld of other types than "text" and "memo" would give an error
On Error Resume Next

For Each fld In tdf.Fields
fld.AllowZeroLength = True
Next fld

Set dbs = Nothing
End Sub

Greetings
Marco P

 

[Douglas J. Steele]

Why not

Sub testAllow()
Dim dbs As Database, tdf As TableDef
Dim fld As Field
Set dbs = CurrentDb
Set tdf = dbs.TableDefs!YourTable

For Each fld In tdf.Fields
If fld.Type = dbText or fldType = dbMemo
fld.AllowZeroLength = True
End If
Next fld

Set dbs = Nothing
End Sub

 

[Marco Pagliero]

Why not

Because my way is dependable: it will do what John expects, "to set
Allow Zero Length = Yes property for all relevant fields automatically"
for whatever version of Access, Office, SQL and you name it.

Your way will fail whenever "memo" or "text" don't have the property or
conversely whenever someone introduces a new data type with the
property.

Greetings
Marco P

 

[Douglas J. Steele]

Unlike some other properties, the AllowZeroLength property always exists.

While I suppose it's possible that an additional type for which the
AllowZeroLengthy is valid might be introduced at some point in the future, I
think it's better to avoid the many errors that will be raised, rather than
ignoring them.