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.
Access 2007
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:
Database Spy
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).
The only way can think of to do it is to cast the NULL values as something else. In Access that would be nz(column_name,-9999) or something. I agree about Access in general, I’m forced to deal with a similar situation – apps built in Access prior to SQL Server being installed with eccentric coding practices, little documentation, etc. The worst part is they never migrated completely to SQL Server so I have Access dbs interacting with SQL Server and vice-versa. Joy! Good luck!