Microsoft KB Archive/210278

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

Article ID: 210278

Article Last Modified on 1/26/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210278



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

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
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.



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. 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.

 Start Microsoft Access and open the sample database Northwind.mdb. Create a module and type the following line in the Declarations section, if it is not already there:

Option Explicit

  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  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.

