Microsoft KB Archive/217156

= PRB: Jet Doesn't Honor AllowZeroLength=No When Table Created With CREATE TABLE SQL Statement =

Article ID: 217156

Article Last Modified on 2/12/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 2000 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 4.0 Enterprise Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q217156



SYMPTOMS
When adding records to a Jet table, the user is allowed to enter empty strings even though the AllowZeroLength field property is set to False.



CAUSE
Microsoft is currently investigating the cause of this bug.



RESOLUTION
You can do one of the following:
 * Create the table in Access.
 * Create the table through DAO TableDef and Field objects.
 * Use DAO to explicitly set the AllowZeroLength property after the table has been created through the CREATE TABLE SQL command.



MORE INFORMATION
There is no provision in the CREATE TABLE syntax to explicitly set the AllowZeroLength property, so you must use the Jet default. The default value is False, but Jet does not appear to enforce this rule unless the value is explicitly set.

Steps to Reproduce Behavior
 Use Microsoft Access to open an existing database or create a new database. Create a new table with a single text field called Description. Leave all other properties at their default values and save with a name of AccessTable. Do not have Access add a Primary key to the table definition.  Create and execute the following SQL statement in Access' query designer:

CREATE TABLE SQLTable (Description TEXT(50))   Create and execute the following SQL statement in Access' query designer:

INSERT INTO SQLTable VALUES ("")

NOTE: The query runs and allows you to insert the empty string, even though if you open the table in Design view, the AllowZeroLength property is set to No (False).   Create and execute the following SQL statement in Access' query designer:

INSERT INTO AccessTable VALUES ("")

NOTE: The query fails to run due to field validation rule violations. This is the correct behavior.   Access 95 and later only: Press the CTRL+G key combination to bring up the Debug/Immediate window and type the following command:

CurrentDB!SQLTable(0).AllowZeroLength = False

NOTE: With Access 2.0 and the other versions, you can use the Access table design user interface to effect the same change by changing the Allow Zero Length field property in table design view to Yes, then changing back to No, and then saving the table design. </li></ol>

When creating tables in code, you can switch to using DAO TableDef and Field objects instead of CREATE TABLE syntax, or you can run the following code immediately after creating the table:

Sub FixAllowZeroLength(td As TableDef) Dim F As Field For Each F In td.Fields If F.Type = dbText Or F.Type = dbMemo Then F.AllowZeroLength = 0 ' reasserting the default value End If Next F End Sub

You would use it as follows:

db.Execute "CREATE TABLE Table1 (Description TEXT(50))" FixAllowZeroLength db!Table1

NOTE: This problem also affects tables created through the ODBC driver for Microsoft Access and the OLEDB provider for Microsoft Jet. It can also affect applications written in other languages, such as C++ and Java.

<div class="references_section">