Thanks,
Jim

Tim F

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