When you use DAO to attach a SQL Server 6.5 table, an attempt to delete records that contain one or more NULL values results in the following error:
Data has changed. Operation stopped.
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 Microsoft Access/Visual Basic that has NULL values. An attempt to delete a row that has one or more NULL fields will not succeed because Microsoft Access/Visual Basic uses the following syntax:
Delete from <tablename> Where <colname1> = NULL And <colname2> = 'xxx'
Because the above search condition is always evaluated to FALSE, no rows will be affected by the delete and Microsoft Access/Visual Basic will display the message, "Data has changed. Operation stopped."
To work around this problem, do one of the following: