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

• Microsoft Access versions 2.0, 7.0

SYMPTOMS

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

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.