Archive for February, 2010

Why Access Sucks: No Subselects in Update Query

February 26, 2010 Leave a comment

Today I found myself in the situation to fix a real nasty but critical application for the Rhein-Main Ergo Cup event about to take place this weekend. It’s a crufty ASP Application written in VB, uses 3 static frames (please don’t shoot me) for “navigation” and it’s built on top of an Access database with strings as primary keys. Please put that gun down, I have left the worst things unmentioned. And I will spare you the actual code as it’s not my application and I do actually believe even by the virtue of fixing it I broke the License in say, some hundreds of points. So, the word legacy doesn’t quite fit it.  It probably has been legacy even before it was written. The code is as procedural as it can get, the notion of a “function” seems to be a woggle among ASP programmers. Instead of using loops and this crazy thing called variables, the original creators decided to copy paste repeating code.

I had to fix bugs in this application before to make it usable for this event and have also written some extensions for it using JScript. It’s been the first application I have ever worked on and I think the reason why I put so strong emphasis on clean and readable code in everything I do today.

Okay, so we needed some functionality this year which the application claimed to support. Testing it, we found it actually did work in some way. The tiny detail that didn’t work was that the application was unable to assign a competitors place correctly by time ascending. So my idea was to execute a simple SQL statement after the script did it’s job and correct that. Having grown up using ORMs my SQL is not all too strong, so I decided asking a quick question on Stackoverflow. Within thirty minutes, I got an answer pointing me in the right direction. The idea was using a Subselect to infer the place of a competitor (a start in this case, each competitor can have multiple starts in different races) by counting the competitors of the same race with better times. Looks like this:

SELECT s.StartsID, s.Time, (SELECT COUNT(*) FROM Starts as raceStarts WHERE raceStarts.LaufID = s.LaufID AND raceStarts.Time < s.Time)+1 as CalculatedPlace
FROM Starts AS s
WHERE s.RaceID = @raceID

Worked very well, though I guess performance of this must be horrible. Doesn’t matter for the case. So next up, I wanted to use this idea for an Update statement.

Update Starts as s SET Place = (SELECT COUNT(*) FROM Starts as raceStarts WHERE raceStarts.LaufID = s.LaufID AND raceStarts.Time < s.Time)+1

WHERE s.RaceID = @raceID

Turns out, Access hates me today and gives the following error message (before that I needed to assure it I wanted to enable “dangerous database content”):

Operation must use an updatable query.

Huh? Any clue what happened? Me neither so after digging around the internet for the next half hour, it seems the only people experiencing this same error are those who have never heard of joins before and prefer to use subselects in all their queries. Great. And on goes the search, which finally found me this nugget of information from an MSMVP who says one should use the DLookup() function to do this.  I can even use it inside “query expressions”, that’s what they name SQL in the Access world I guess. A short “binging” (I like the analogy to “banging”) on MSDN reveils, I’d actually want to use the DCount() function, no wait a sec, I don’t because I can’t specify my complicated criteria (time must be shorter than current rows’ time) with it. Great.

So in the spirit of a really cool Sam Fisher move I decided to hack a little bit of ugly code that will perfectly melt with it’s environment:

sql = "SELECT s.StartsID, s.Time, (SELECT COUNT(*) FROM Starts as raceStarts WHERE raceStarts.LaufID = s.LaufID AND raceStarts.Time < s.Time)+1 as CalculatedPlace<span style="font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: xx-small;"><span style="white-space: normal;"> </span></span>FROM Starts AS s WHERE s.RaceID = &LaufID
 RS.OPEN sql,nameConn
	sql = "UPDATE Starts SET Platz='"&RS("CalcPlace")&"' WHERE StartsID="&StartsID
Categories: General

3D Cinema has finally arrived

February 13, 2010 Leave a comment

I’m right now sitting in Avatar and it’s frickin awesome. This is the revolution of cinema. If they would now be so kind to produce 60p content…

Categories: General
%d bloggers like this: