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

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

1. Use Microsoft Access to open an existing database or create a new database.
2. 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.
3. Create and execute the following SQL statement in Access' query designer:
   

        CREATE TABLE SQLTable (Description TEXT(50))
      
   						

4. 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).
5. 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.
6. 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.