[AccessD] change all fields to Allow Zero Length via VBA

The AllowZeroLengthString property is exposed through the Field object 
children of the TableDef object. The solution is simply a matter of 
programmatically changing this property for all fields in a selected 
table, or indeed in all tables in a database.

Here's the code: courtesy Lamont Adams

Public Sub ChangeZeroLengthSetting(strTableName As String)
On Error Resume Next
Dim db As Database
Dim tdf As TableDef
Dim fld As Field

'Try to get a pointer to the selected table
Set db = DBEngine(0)(0)
Set tdf = db.TableDefs(strTableName)

'Oops
If Err.Number Then
MsgBox Err.Description
Set tdf = Nothing
Set db = Nothing
Exit Sub
End If

'Now we have the tabledef open, iterate through the
'fields collection, and change the appropriate property
For Each fld In tdf.Fields
If fld.Type = DB_TEXT Or fld.Type = DB_MEMO Then
'AllowZeroLength property is only available for
'text or memo fields
fld.AllowZeroLength = True
End If
Next fld

'Clean up
Set tdf = Nothing
Set db = Nothing

End Sub

This method can be applied to change other properties, assuming the 
desired property is write-able from DAO—many aren't. All tables in a 
database can be changed with the addition of an outer For each tdf in 
db.Tabledefs loop; just be sure to examine the Attributes property for 
the dbSystemObject bit. Access will not let you change the field 
properties for one of the System tables.

Bob Heygood wrote:

>Good Morning to the list,
>
>I find myself with 2 new tables I imported from Excel with over 321 fields.
>All fields are text 255.
>I don't think I can face manually changing all of them to "Allow Zero Length
>String".
>
>In the archives I could not find a way to do so easily.
>Some thing like iterate thru all the fields in the table via DAO, changing
>the property....
>
>Any code out there?
>
>A2K
>
>TIA
>
>Bob Heygood
>
>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada