PRB: Deleting Records Containing NULLs Using DAO

Last reviewed: April 15, 1997
Article ID: Q152021

The information in this article applies to:
  • Microsoft SQL Server, version 6.5

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.


Additional query words: MFC DAO Access Jet 3197
Keywords : kbnetwork SSrvGen SSrvVisB
Version : 2.65.0201
Platform : WINDOWS
Issue type : kbprb


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 15, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.