Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

This article was previously published under Q217156

↑ Back to the top


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.

↑ Back to the top


Cause

Microsoft is currently investigating the cause of this bug.

↑ Back to the top


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.

↑ Back to the top


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.

↑ Back to the top


References

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

150418 How To Create an Access Database Through Visual Basic 4.0

↑ Back to the top


Keywords: kbdatabase, kbjet, kbprb, KB217156

↑ Back to the top

Article Info
Article ID : 217156
Revision : 5
Created on : 2/12/2007
Published on : 2/12/2007
Exists online : False
Views : 625