ACC: Error Deleting Value from Linked SQL Server Table
ID: Q117616
|
The information in this article applies to:
-
Microsoft Access versions 2.0, 7.0, 97
SYMPTOMS
Moderate: Requires basic macro, coding, and interoperability skills.
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 <column
name> in table <table name> 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.
MORE INFORMATION
Steps to Reproduce Problem
- Create a table on SQL Server with a column with the NOT NULL
restriction.
- Start Microsoft Access and open any database.
- Link the table that you created in step 1.
- Enter a value in the column with the NOT NULL restriction.
- Move to the next column.
- Move back to the previous column, select the value, and then delete it.
- Commit the record by moving to another record. Note that you receive
the error message mentioned in the "Symptoms" section.
REFERENCES
For more information about using zero-length strings, search the Help Index
for "zero-length strings," or ask the Microsoft Access 97 Office Assistant.
For additional information about zero-length strings and SQL Server,
please see the following article in the Microsoft Knowledge Base:
Q117614 Error Adding Zero-Length String to SQL NOT NULL Column
Keywords : kberrmsg kbinterop OdbcSqlms
Version : 2.0 7.0 97
Platform : WINDOWS
Issue type : kbbug