ACC: How to Set AllowZeroLength Property to Yes in All Tables

MORE INFORMATION

By setting the AllowZeroLength property, you can control whether a zero-length string ("") is a valid entry for Text and Memo fields. The default setting for the AllowZeroLength property is No, but you can set the AllowZeroLength property to Yes for every table in a database by following these steps.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb (or NWIND.MDB in version 2.0 or earlier). You may want to back up the Northwind.mdb (or NWIND.MDB) file and perform these steps on a copy of the database.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

1. Create a module and type the following line in the Declarations section, if it is not already there:

   Option Explicit
   					

2. Type the following procedure:

         Function SetAllowZeroLength ()
             Dim I As Integer, J As Integer
             Dim db As Database, td As TableDef, fld As Field
   
             Set db = CurrentDB()
             'The following line prevents the code from stopping if you do not
             'have permissions to modify particular tables, such as system
             'tables.
             On Error Resume Next
             For I = 0 To db.TableDefs.Count - 1
                Set td = db(I)
                For J = 0 To td.Fields.Count - 1
                   Set fld = td(J)
                   If (fld.Type = DB_TEXT Or fld.Type = DB_MEMO) And Not _
                     fld.AllowZeroLength Then
                      fld.AllowZeroLength = True
                   End If
                Next J
             Next I
             db.Close
         End Function
   					

3. To test the function, type the following line in the Debug window (or Immediate window in version 2.0), and then press ENTER:

   ? SetAllowZeroLength()

   Note that after a few seconds, the AllowZeroLength property is changed to Yes for all the Text and Memo fields in every table in the database.

NOTE: By changing the If...Then condition and the assignment that immediately follows it in the code above, you can loop through the tables to modify the following field properties also: Name, ValidationRule, ValidationText, Required, and DefaultValue.