Jump to content
Sign in to follow this  
Mr. Crimson

Updating Last Two Records in Access

Recommended Posts

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. :P

Please let me know if you need more info.

Best Regards-

Mr. Crim

Edited by Mr. Crimson

Share this post


Link to post
Share on other sites
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.


[font="Fixedsys"][list][*]All of my AutoIt Example Scripts[*]http://saneasylum.com[/list][/font]

Share this post


Link to post
Share on other sites
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 by Mr. Crimson

Share this post


Link to post
Share on other sites
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 by lod3n

[font="Fixedsys"][list][*]All of my AutoIt Example Scripts[*]http://saneasylum.com[/list][/font]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×