PRB: Deleting Records Containing NULLs Using DAO (152021)

SYMPTOMS

When you use data access objects (DAO) to attach a SQL Server 6.5 table, an attempt to delete or update records that contain one or more NULL values results in the following error:

Data has changed. Operation Stopped

CAUSE

The SQL Server 6.5 ODBC driver always sets the ANSI_NULLS option to ON on every connection that is to be ANSI compliant. This means that a search condition like 'WHERE <colname> = NULL' is always evaluated to FALSE. In accordance with the ANSI specification, the correct syntax when searching for NULLs is 'WHERE <colname> IS NULL'.

Consider a SQL Server table attached in Access/Visual Basic that has NULL values. An attempt to delete a row that has one or more NULL fields will not succeed, because Access/Visual Basic uses the following syntax:

   Delete from <table-name> Where <colname> = NULL And <colname> =
   'xxx'...............

Because the above search condition is always evaluated to FALSE, no rows will be affected by the delete and Access/Visual Basic will display the message, "Data has changed. Operation stopped."

WORKAROUND

To work around this problem, do one of the following:

Update the NULL values to non-NULLs and then delete the record

-or-
Add a timestamp (a SQL Server data type) column to the SQL Server table that does not allow NULLs, and copy the data from the existing tables to the new ones. Then, delete the existing tables and rename the new tables.