Developer

InAccess: Querying .Net

INAccess: Tales of woe revolving around the Access Database system

I've been working in .NET for a long time now, and I have the unfortunate pleasure of using Microsoft Access for my databasing needs. Sure, Access is fine for (very) elementary databasing needs, and it's handy because it comes prebundled with the rest of Office, but it many ways it seems to have become the forgotten brother of the office package.



The forgotten, mentally inept brother.



My biggest surprise was recently, when calling a stored procedure from within .NET I do this a lot, and it's no big deal, but in this instance, it returned me a different number of results than when I executed the query from within the Access itself. It's the same query — no parameters or anything extraordinary — just different results. I ran the query in the database and got 5 results, and then called the same query from within .NET and got only 1 result. I spent 2 hours looking through my code, figuring out what I'd done wrong. It just seemed ludicrous that it could actually be an internal error. I was wrong.



For whatever reason, when calling a stored procedure in access from within .NET, the SQL within in the procedure is interpreted differently than when it is called from within Access itself. It boils down to the following:



SELECT *

FROM tGodForsakenAccessTable forsaken

WHERE forsaken.Name LIKE "%Text%"



In a LIKE comparison, the '%' character is the "wildcard" character. However, when calling the exact same procedure (the procedure that is stored within the database itself), through .Net, the query expects an asterix as the wildcard.



Now, if I was typing to query into .NET and sending it through, I could live with it being handled two different ways. However, this issue means that in order to use stored procedures in .NET, it is NECESSARY that they don't work within Access itself whenever you wish to use wildcards.



My current work around is the following:

SELECT *

FROM tGodForsakenAccessTable forsaken

WHERE forsaken.Name LIKE "%Text%"

OR forsaken.Name LIKE "*Text*"



My future work around will be to not take jobs using Access. The downsides of this hack, apart for the fact, it's a filthy, filthy hack is:

a) The bloated size of the query

b) Updating the query becomes a pain

c) Reading the query becomes a pain

d) It's uglier than a brown Zune player

e) I'm also not sure whether or not Access/VB.Net will return extra results with percentage signs or asterixes in them or do something else inventive. I didn't test it since I was too busy punching my monitor, printing out reams of profanity and listening to the Cure.


Defeat.

Editor's Picks

Free Newsletters, In your Inbox