ACC: Error Deleting Value from Linked SQL Server Table

• 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. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

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.

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: