Bug with Access and SQL Server: Erroneous #Deleted bug on Insert with null values
When a new row is inserted into a view that uses identity but contains null values for at least one field and Access' subsequent attempts to locate the new row uses "a_field = NULL" and thus will always fail to find that row and return #Deleted.
As viewed in this thread Access 2007 bug with SQL Server Back End, a user AVG has an open case with Microsoft and has been told that it is a bug. Fellow Access MVP Ben Clothier can reproduce this in Access 2003 and against a base table as well so it's not restricted to views only. Because the scope includes even plain old tables this can be problematic and very confusing due to its intermittent nature.
Paragraphs added: "and against a base table as well so it's not restricted to views only. Because the scope includes even plain old tables..."
The tables should be qualified to Non-SQL Sever linked tables. This occurs because from I can tell, Access will only look for keyword "identity" against linked tables that are said to be from SQL Server. Any other ODBC linked table will be more vulnerable to that bug because Access will not make a call to "SELECT @@identity;" if it's not a SQL Server linked tables. This bug occurs whenever Access has to fall back on the last resort of comparing every fields. End added
As the user AVG noted, he was following advice from MSDN article Optimizing Microsoft Office Access Applications Linked to SQL Server by Andy Baron regarding using a view and INSTEAD OF trigger:
Thanks to Ben Clothier for writing the above summary and AVG for finding the problem and reporting it in the newsgroups. (I’ll take a small amount of credit for urging AVG to post the problem when he was asking about problems in SQL Server.) Fellow newsgroup denizens Banana and David W. Fenton did a great job with suggestions in that thread.