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

This article shows you how to create a user-defined function to set the AllowZeroLength property to Yes for all the Text and Memo fields in every table in a database.

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. To set the AllowZeroLength property to Yes for every table in a database, follow these steps:

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. Create a module and type the following line in the Declarations section, if it is not already there:
   Option Explicit
3. Type or paste the following procedure:

   Function SetAllowZeroLength ()
       Dim I As Integer, J As Integer
       Dim db As DAO.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
   					

4. To test the function, type the following line in the Immediate window, 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 also modify the following field properties: Name, ValidationRule, ValidationText, Required, and DefaultValue.

For more information about the AllowZeroLength property, click Microsoft Access Help on the Help menu, type allowzerolength property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.