Updating Last Two Records in Access

[Mr. Crimson]

Howdy all-

I'm a bit at a loss so here goes nothing...

I have an Access database which I'm attempting to do an UPDATE to using AutoIt, the trick of course, being that I only want to update the latest two records in the list.

Here's sort of what I want to do, but the example below does not work as hoped, and I get an error that the action with that object has failed:

$cmd.CommandText = "Update patchlist SET patchstatus = 'notinstalled' From (Select Top 2 patchID From patchlist Order By patchID DESC) AS Latest Where patchlist.patchID = Latest.patchID"

I can do straight up UPDATE and SELECT statements, but this one has been a total bear... it doesn't help that I'm still a bit of a SQL Query n00b.

Please let me know if you need more info.

Best Regards-

Mr. Crim

Edited August 17, 2006 by Mr. Crimson

[lod3n]

In the help file, look up ObjEvent. It has a basic COM error handler that you should throw into your script. What it will do is pop up the actual error that Access is throwing back at you.

I suspect there is an error in your querystring, but I can't tell without looking into it deeper. The COM error should pinpoint the syntax problem if there is one.

[Mr. Crimson]

In the help file, look up ObjEvent. It has a basic COM error handler that you should throw into your script. What it will do is pop up the actual error that Access is throwing back at you.

I suspect there is an error in your querystring, but I can't tell without looking into it deeper. The COM error should pinpoint the syntax problem if there is one.

Thank you for the reply, here's the error message it gives:

err.description is: [Microsoft][ODBC Access Driver] Syntax error (missing operator) in query expression "Available' From (Select Top 2 patchID From patchlist Order By patchID DESC) AS Latest'.

err.number is: 80020009

err.scriptline is: 41

Best-

C

Edited August 17, 2006 by Mr. Crimson

[lod3n]

Well, that's great, that means it is Access generating the error. At least you've narrowed that down. But I expected a more detailed error. Try Svenp's custom MyErrFunc(), it can return more information:

http://www.autoitscript.com/forum/index.php?showtopic=22160

Edited August 17, 2006 by lod3n

[Mr. Crimson]

I got it!

$cmd.CommandText = "Update patchlist SET patchstatus = 'Available' WHERE patchID in (Select Top 2 patchID From patchlist Order By patchID DESC)"

Best-

Crim

[lod3n]

Great!