ACC: How to Set AllowZeroLength Property to Yes in All Tables (130336)

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

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.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

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.

Create a module and type the following line in the Declarations section, if it is not already there:
```
Option Explicit
					
```

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

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.

ACC: How to Set AllowZeroLength Property to Yes in All Tables (130336)

SUMMARY

MORE INFORMATION

REFERENCES