Access 2007 and NULLs

I wasted several hours not realizing that MS Access 2007 doesn’t display NULLs in a table.

It all started when we’ve been asked to patch up an old project whose authors have gone missing. The application was ASP.NET 1.1 with an Access db and no source code and no documentation. Oh, the joy!

The database has kept us busy for hours, as it did not behave as it was supposed to. We had to use a decompiler to get the SQL Queries from the code and even then it took a while after it hit us: some fields contained empty strings, while others contained NULLs. Apparently, for all its pretty interface, Access can’t show the difference.

Can you tell which fields contain empty strings and which contain NULL values? Neither could I

Finally, I opened Altova Database Spy and checked the same table with it:

With Database Spy the differences are obvious

Of course, I don’t know why anybody would use Access instead of SQL Server (especially since the latter has a free Express version which is still more powerful than Access) but that’s another story. It still amazes me that I wasted 4 hours over something that should have been obvious. (by the way: I checked the Access help and settings and still couldn’t find a way to make it display the NULL values).