Microsoft KB Archive/117614

= ACC: Error Adding Zero-Length String to SQL NOT NULL Column =

Article ID: 117614

Article Last Modified on 7/8/2002

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition

-



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 in table 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.



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.

