Microsoft KB Archive/117616

= ACC: Error Deleting Value from Linked SQL Server Table =

Article ID: 117616

Article Last Modified on 1/19/2007

-

APPLIES TO


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

-



This article was previously published under Q117616



Moderate: Requires basic macro, coding, and interoperability skills.



SYMPTOMS
When you link (attach) an SQL Server table that has a column with a NOT NULL restriction in Microsoft Access, add a value to the restricted column in the linked table, then delete the value and attempt to commit the record, you may receive the following error message:

[Microsoft][ODBC SQL Server Driver][SQL Server] The column in table may not be null. (#233)



CAUSE
When the value is deleted, Microsoft Access first attempts to write a null to the restricted column in the linked table. This results in an internal error from SQL Server, and Microsoft Access then attempts to write a zero- length string. Because SQL Server does not recognize zero-length strings, it treats the string as a null, resulting in the error message.



RESOLUTION
Do not enter zero-length strings in SQL columns with the NOT NULL restriction.



STATUS
Microsoft has confirmed this to be a problem in Microsoft Access 2.0, 7.0 and 97.



Steps to Reproduce Problem

 * 1) Create a table on SQL Server with a column with the NOT NULL restriction.
 * 2) Start Microsoft Access and open any database.
 * 3) Link the table that you created in step 1.
 * 4) Enter a value in the column with the NOT NULL restriction.
 * 5) Move to the next column.
 * 6) Move back to the previous column, select the value, and then delete it.
 * 7) Commit the record by moving to another record. Note that you receive the error message mentioned in the "Symptoms" section.

