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.

Additional query words: inserting attaching

Keywords : kberrmsg kbinterop OdbcSqlms
Version : 2.0 7.0
Platform : WINDOWS
Issue type : kbprb