Microsoft KB Archive/152021

= PRB: Deleting Records Containing NULLs Using DAO =

Article ID: 152021

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q152021



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 = NULL' is always evaluated to FALSE. In accordance with the ANSI specification, the correct syntax when searching for NULLs is 'WHERE 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  Where = NULL And = '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.

Additional query words: MFC DAO Access Jet 3197

Keywords: kbnetwork kbprb KB152021

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.