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

ID: Q217156

The information in this article applies to:

Microsoft Access versions 2.0, 2000, 7.0, 97
Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 4.0, 5.0, 6.0

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.

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.

REFERENCES

For additional information about creating tables using TableDef and Field objects, please see the following article in the Microsoft Knowledge Base:

Q150418 How To Create an Access Database Through Visual Basic 4.0

Additional query words: kbdse

Keywords : kbDAO kbDatabase kbJET kbVBp kbVBp400 kbVBp500 kbVBp600
Version : WINDOWS:2.0,2000,4.0,5.0,6.0,7.0,97
Platform : WINDOWS
Issue type : kbprb