PRB: Deleting Records Containing NULLs Using DAOLast reviewed: April 15, 1997Article ID: Q152021 |
The information in this article applies to:
SYMPTOMSWhen 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 CAUSEThe 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."
WORKAROUNDTo work around this problem, do one of the following:
|
Additional query words: MFC DAO Access Jet 3197
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |