Microsoft KB Archive/130336

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

Article ID: 130336

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q130336





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.

