Access != database

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.

3 thoughts on “Access != database”

  1. That’s why there is the VBA option “option compare database” that Access uses by default for all VBA code. I think case insensitiveness is also the default behavior for SQL server and most other DBMS — so I think Access is not really to blame here… But that does not change the fact that Access can be a pain to work with 😉

  2. Most MS SQL Server installation are also ‘case insensitive’, like Jet. So, based from your comment, changing Jet to MS SQL Server everywere, yield the same observable effects, so,… MS SQL Server ain’t a database?

    Na, sounds more you are just too fast to blame other of your (wrong) assumptions about code.

  3. It is true that my asumption on the default with SQL server was wrong. I checked, and the default is insensitive.

    With SQL Server you can choose whether you want case sensitive or not. And if you have a database in which strings are keys, then case sensitivity is the only way to insure data integrity when the clients are case sensitive.

    With Access you can’t, so you are forced to implement case insensitivity in the client, in each place where you use those keys for joinings or lookup.

    Normally I’d use identity ints, but this is a database I got from someone else, so string it was.

Leave a Reply

Your email address will not be published. Required fields are marked *