
TableDef/Set all text fields to allow zero length
How to use Code to Set Allow Zero Length Strings in ALL Access Tables:
(Most of the credit for this goes to Cheryl Fischer. )
Make sure that you have a reference to DAO.
(Open a code module, Tools, References.)
Copy this code to a module and run it in the Immediate Window.
(Press Ctrl-G, then type the name of the procedure and press Enter.)
The code will check each table to see that if it is an ODBC attached table
or an ISAM attached table or an Access System table. It skips over those
types of tables.
Hopefully, all other tables are regular Access tables!
Then it checks each field to see if the type is Text or Memo (which includes
hyperlink.)
If it is, then it changes the AllowZeroLength to True.
If you need to exclude a table for some reason, you will need to modify the
code as it does ALL Access tables!
There is also error handling to catch the case where a table is open and the
property can't be modified. It ignores that field and keeps going.
Public Sub ChangeZeroLength()
On Error GoTo Err_ChangeZeroLength
Dim tdf As TableDef
Dim db As Database
Dim fld As Field
Set db = CurrentDb
For Each tdf In db.TableDefs
'Debug.Print tdf.Name
If (tdf.Attributes And dbAttachedODBC) Or (tdf.Attributes And
dbAttachedTable) Or (tdf.Attributes And dbSystemObject) Then
'do nothing
Else
For Each fld In tdf.Fields
'Debug.Print fld.Name, fld.Type
'Note: dbMemo also includes hyperlink fields!
If fld.Type = dbText Or fld.Type = dbMemo Then
fld.AllowZeroLength = True
End If
Next fld
End If
Next tdf
Set db = Nothing
MsgBox ("All text, Memo and Hyperlink fields now allow zero length
strings!")
Exit_ChangeZeroLength:
Exit Sub
Err_ChangeZeroLength:
Select Case Err.Number
Case 3422 'table is in use
Resume Next
Case Else
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source &
vbCrLf & Err.Description, , "SystemCode - ChangeZeroLength"
Resume Exit_ChangeZeroLength
End Select
End Sub
--
Joe Fallon
Access MVP
Quote:
> I have created a TableDef and I want to be set all text
> fields properties to Allow Zero Length = True using VBA
> code. I would appreciate any assistance.
> Thanks,
> Jim