Sign in to follow this  
Followers 0
Mr. Crimson

Updating Last Two Records in Access

6 posts in this topic

#1 ·  Posted (edited)

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



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

#3 ·  Posted (edited)

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

#4 ·  Posted (edited)

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

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  
Followers 0