Back in June, I submitted this post and received a lot of help, but
unfortunately, it could not be resolved as desired.
http://www.developersdex.com/vb/mess...2899&r=6923328
I had mentioned that I had an open case with Microsoft.
Well, today they closed the case.
Here is the official response -

We have confirmed this is a bug in Access. We send a WHERE clause that
includes "<fieldName> = NULL" instead of "<fieldName> IS NULL". This does
not return any records and Access treats this as if the record were deleted
even though it does still exist.

The bug will be reviewed for future releases and/or updates, but right now
there are no immediate plans to address it.

--

AG
Email: npATadhdataDOTcom

There are no values, expressions, fields, strings, etc etc equal to
null.

So there can be no records where some field = null.

So the SELECT * FROM [MS] WHERE [Brain] = Null should return no
records even though there are a lot of null brains over there.

Whereas SELECT * FROM [MS] WHERE [Brain] Is Null should return the
records where brain Is Null, the whole shooting match in my
experience.

So why is this a bug?

Maybe my brain is null on this and there's an explanation?

I haven't experimented with this; somewhere sometime the = Null
construction has worked for me; I think it's in sending SQL strings
off to asp.Net from within vb ... maybe. But I've always thought it
was the code interpreting the string rather than SQL accepting = Null.


On Sep 1, 3:53*pm, "agiamb" <NOSPAMagi... (AT) newsgroup (DOT) nospam> wrote:

On 9/1/10 2:01 PM, lyle fairfield wrote:
The bug is somewhat specific - we will see it if Access is forced to
fall back to its last resort but I'm getting ahead of myself.

In general, whenever we insert a new record, Access will send a request
to the backend to locate the newly inserted record. With SQL Server,
it'll send a "SELECT @@identity;". With other ODBC backend, it depends
on whether the ODBC driver will help Access out.

But when we use a SQL Server view or an ODBC backend that does not help
Access out, Access notes that it can't be certain that SELECT @@identity
will succeed so it turns to the last resort of doing a:
"SELECT pk FROM a_table WHERE a_col = a_value AND b_col = b_value ...
n_col = n_column;"

where the WHERE criteria is built based on how the user dirtied the
fields. If the user never dirty a field, then it's not a part of the
criteria. This will work as long there is exactly one row returned. If
Access get zero or more than one, Access gives up and mark the newly
inserted row as #Deleted even though in fact the insertion was successful.

If you've sticked with me so far, now here's how null comes in the
picture. If an user dirty the field, move out, and changes the mind and
clear out the content... _before_ saving, then proceeds to save the
field, Access will then build the WHERE criteria and notice that the
field was dirty and makes it so:
"SELECT pk FROM table_a WHERE ... col_x = NULL..."

which is of course impossible and we get nothing. The result is a
#Deleted. Note that this will not occur if Access sends in a @@identity
instead of comparing every field. Access will basically send in
@@identity only when it knows that a certain column on linked table is
in fact the primary key. That key icon won't show up in table design if
we link a SQL Server link or any other ODBC backend.

I did post a workaround by basically collecting all new values from
dirtied fields, then undoing then re-apply the dirtied fields back and
proceed to save the field. This way, any dirty nulls will not impact
this problem (though we are still stuck with the not so great last
resort of comparing every field which will certainly fail should a table
contain two rows with different pk but otherwise identical content for
non-pk columns.

Tangent:

I did have a looky-see in PostgreSQL ODBC driver source code and was
intrigued to find the following:

They added a special flag to basically lie to Access that PostgreSQL is
SQL Server in order to coax the Access to send in more reliable "SELECT
@@identity;" and go out of their way to convert the @@identity into a
currval(). Furthermore, I did not notice that until long after the OP
posted last post in the June but PostgreSQL did have a setting to
recongize "col = NULL" and convert it to "col IS NULL" thus "fixing" the
bug and the comment suggested that they added this for Access so they
had to have knew about that bug long before the OP posted this. Whether
their fix is a good is questionable but at least it's off by default and
isn't easy to turn on, but well, there you go.

So in conclusion, Access team did do one thing right - they didn't try
to assume that any ODBC backend would use the same @@identity trick to
find the key and because ODBC does not supply that information, they had
to do that "compare-every-fields" trick to locate the new record to add
to the form after the user has submitted the content. But geez, they
stop short of giving a one simple property that could have been added to
linked tables that would allow the developer to define how to find the
key. Bam! All that quirky #Deleted problems would go away peasy-easy.
But they felt that it'd be too "programmatic" and not something an
Access user would be ever interested in setting. Oh well.

Hope that made for a enjoyable bedtime reading.

On Wed, 01 Sep 2010 16:06:44 -0700, Banana <Banana (AT) Republic (DOT) com>
wrote:

Actually that was very interesting.

Thanks, Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

Correction:

On 9/1/10 4:06 PM, Banana wrote:
It should be read "SQL Server View", not "SQL Server link" which is
obviously nonsensical!

lyle fairfield <lyle.fairfield (AT) gmail (DOT) com> wrote in
news:b8057613-bba8-4a14-85e1-2a3080429666 (AT) d8g2000yqf (DOT) googlegroups.com
:

Because nothing is every equal to Null?

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Hey, Lyle... good to hear from you, again. Hope all's well with you.

Larry