Access != database

vanDooren — Mon, 14 Jul 2008 06:27:00 GMT

While working on a data application for the finance guys, I already discovered that Excel sucks when used with OleDb. It seems as if Excel was specifically designed to make your life as a programmer miserable.

Fair’s fair: Excel is not a database, so perhaps it is acceptable if the data link layer feels as if it has been thrown together by a vba hacker without a clear grasp of data types and interfaces. Probably they needed another handful of buzzwords on the Excel box, and decided to hire a high-school summer intern who installed basic once.

But I digress.

While I am willing to let Excel off the hook for not being something it was not supposed to be, I have a problem with extending the same courtesy to Access; an application specifically designed to be a database.

For the last couple of hours I have been debugging the finance app because some of the allocated items showed up as having cost nothing. This would have enjoyed the CFO immensely if it had been true, but since that wasn’t the case, they were wondering why my app insisted it. For some systems the totals and the details were correct. For others they were zero, or something far less that what they had cost in reality.

I examined a correct example, compared it with one of the problem cases, and found that all required data was there, in the correct tables. Then I debugged through the code, and the algorithm seemed to work just fine. Only for some systems it didn’t return any values. And since this was done via LINQ, debugging the actual SQL was difficult.

Then I decided to go spelunking in the raw data tables themselves. Again and again I checked, and nothing seemed to be wrong. But then it suddenly jumped at me:

tbl_keymstr -> row 1234 -> Alloc_key == ‘all’

tbl_alloc -> row 123456 -> Alloc_key == ‘All’

How could this be possible? I double checked, and verified that the links were all defined with the ‘Enforce referential integrity’ flag set.

After some testing, it seems that Access is pretty loose in its judgments. When it comes to comparing strings, ‘equals’ really means ‘kinda looks like’, so ‘All’, ‘all’ and ‘alL’ are all the same for access.

The LINQ queries in my app all use the C# ‘==’ to define joinings and equality operations, so if the rows in 1 table say ‘All’ and those in the other say ‘all’ the result of the joined set is empty of course.

Key definitions and cost allocations get uploaded separately, and my app doesn’t check whether the key fields are spelled the same. Everything gets uploaded, and the integrity is insured by the database itself, as it should be. If there is an upload error it gets reported and everything is rolled back. Unfortunately, key value comparison does not work that well.

The fix was very simple: I implemented a case insensitive comparison in my queries, and the problem was solved. Too bad Access is allowed by the Office team to let sloppy input through.

EDIT: I just checked, and by default, SQL server has the same behavior. But you can change it to case sensitive if you want, which is important in scenarios like these. Granted, I should have checked first before slagging off Access, but I still think that this should be an option in Access. The fact that it isn't is a severe shortcoming in my eyes, since lots of smaller databases use strings as keys. In case sensitive clients (like e.g. C# apps by default) this causes a lot of work to get correct results. And if you forget to case insensitivize (is that even a word?) your queries even once, you will get incorrect data.

Cluebat-man to the rescue : Access

Access != database