Home > General > Why Access Sucks: No Subselects in Update Query

Why Access Sucks: No Subselects in Update Query

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
 DO WHILE NOT RS.EOF
		StartsID=RS("StartsID")
	sql = "UPDATE Starts SET Platz='"&RS("CalcPlace")&"' WHERE StartsID="&StartsID
	response.write(sql&"<br>")
nameConn.Execute(sql)
	RS.MOVENEXT
       LOOP
	RS.CLOSE
Advertisements
Categories: General
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: