ACC: Error Adding Zero-Length String to SQL NOT NULL Column (117614)


The information in this article applies to:

  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
This article was previously published under Q117614
Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

When you link (attach) a SQL Server table that was created with a NOT NULL restriction on a column, and then try to enter a zero-length string in the table, you may receive the following error message:
[Microsoft][ODBC SQL Server Driver][SQL Server] The column <column name> in table <table name> may not be null. (#233)

CAUSE

Microsoft Access supports zero-length strings, but SQL Server does not. The SQL ODBC driver does not convert the zero-length string to a value that can be used by SQL Server; therefore, SQL Server interprets the zero-length string as a Null value and returns the error message.

RESOLUTION

Either remove the NOT NULL restriction on the SQL Server column, or send some character other than a zero-length string to the SQL Server column.

MORE INFORMATION

Steps to Reproduce Behavior


  1. Create a table on SQL Server with a column with a NOT NULL restriction.
  2. Start Microsoft Access and open any database.
  3. Link to the SQL Server table you created in step 1.
  4. Open the linked table and enter a zero-length string in the field with the NOT NULL restriction. Note that you receive the error message mentioned earlier in this article.

REFERENCES

For more information about zero-length strings, search for "zero length strings, prevent or allowing in fields," and then "Properties that control how blank fields are handled" using the Microsoft Access 7.0 Help Index.
Modification Type:MajorLast Reviewed:7/8/2002
Keywords:kberrmsg kbinterop kbprb KB117614
©2002 Microsoft Corporation. All rights reserved.