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

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

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

This article demonstrates a user-defined function that you can use 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). You may want to back up the Northwind.mdb file or perform these steps on a copy of the Northwind 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.

REFERENCES

For more information about the AllowZeroLength property, search the Help Index for AllowZeroLength property" or ask the Microsoft Access 97 Office Assistant.

Keywords          : kbprg PgmHowTo PgmLoop PgmObj MdlDao
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto