Jump to content
Sign in to follow this  
dimchik

Help Updating Access DB record

Recommended Posts

dimchik

I am some what new to DB land. I am using

$oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\OfficeCDs.mdb") this string to open and read db

i am getting records evrything works fine but now i need to update a field. Any ideas or general statements or examples how to update fields. I do know sql query design so SQL is not a problem for me.

Thank you for your help

Share this post


Link to post
Share on other sites
zfisherdrums

Hello dimchik,

I grabbed this from w3schools:

The Update Statement:

The UPDATE statement is used to modify the data in a table.

Syntax:

UPDATE table_name

SET column_name = new_value

WHERE column_name = some_value

Hope it helps!

Zach...

Edited by zfisherdrums

Share this post


Link to post
Share on other sites
Klaatu

I made a post a while back about how I do it. See if any of that helps. Good luck.


My Projects:DebugIt - Debug your AutoIt scripts with DebugIt!

Share this post


Link to post
Share on other sites
dimchik

I tryed but the reference is for vb and much of explanation.

Share this post


Link to post
Share on other sites
zfisherdrums

You may also wish to check out the MS ADO online reference. You will have to port some of the examples over to AutoIt, but that shouldn't be too hard.

What in particular are you having difficulty with?

Zach...

Share this post


Link to post
Share on other sites
dimchik

This is what i tried to do

$oConn = ObjCreate("ADODB.Connection")

$oRS = ObjCreate("ADODB.Recordset")

$oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\OfficeCDs.mdb")

$oRS.open("SELECT times_used from table1 where id=4", $oConn, 1)

$oRS.RecordCount

$oRS.MoveFirst

$oRS.edit

$oRS.Fields("Times_used").value=10

$oRS.update

msgBox(4096, "test1", $oRS.Fields("Times_used").value)

$oConn.Close

$oConn = 0

What i am trying to achieve is to increase the times_used value

as i would like to identify how many times a specific cd key was used and the on next use increment field times_used

and I get error

>"C:\Program Files (x86)\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Documents and Settings\dpetrovski\Desktop\testing scripts\WinMenuSelectItem.au3"

C:\Documents and Settings\dpetrovski\Desktop\testing scripts\WinMenuSelectItem.au3 (47) : ==> The requested action with this object has failed.:

$oRS.edit

$oRS.edit^ ERROR

>Exit code: 1 Time: 3.029

Edited by dimchik

Share this post


Link to post
Share on other sites
Klaatu

$oRS.open("SELECT times_used from table1 where id=4", $oConn, 1)

$oRS.RecordCount

$oRS.MoveFirst

$oRS.edit

$oRS.Fields("Times_used").value=10

$oRS.update

msgBox(4096, "test1", $oRS.Fields("Times_used").value)

$oConn.Close

Here's my thoughts:
  • on the recordset open, use 2 as the last parameter, as I think that'll make the recordset updatable.
  • I don't think you need the recordcount thing there so remove it.
  • drop the "edit" line altogether. I don't think you need it. (see the link in my other post for a working example)
  • Try using index numbers instead of field names, ie, use 0 instead of "Times_used".
HTH

My Projects:DebugIt - Debug your AutoIt scripts with DebugIt!

Share this post


Link to post
Share on other sites
dimchik

Ok I tried as you said index is working as if i try to msgit out its comming up but update is not working and i tried 0 1 2 3 4 as last parameters in open connection.

$oConn = ObjCreate("ADODB.Connection")

$oRS = ObjCreate("ADODB.Recordset")

$oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\OfficeCDs.mdb")

$oRS.open("SELECT times_used from table1 where id=4", $oConn, 2)

$oRS.MoveFirst

$oRS.Fields(0).value=2

$oRS.update

msgBox(4096, "pull with index", $oRS.Fields(0).value)

$oConn.Close

$oConn = 0

"C:\Program Files (x86)\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Documents and Settings\dpetrovski\Desktop\testing scripts\WinMenuSelectItem.au3"

C:\Documents and Settings\dpetrovski\Desktop\testing scripts\WinMenuSelectItem.au3 (45) : ==> The requested action with this object has failed.:

$oRS.Fields(0).value=2

$oRS.Fields(0).value=2^ ERROR

>Exit code: 1 Time: 0.519

I tried to find your post unfortunatelly i can not i am already on 5th page and nothing you had some samples they wore with insert statement and not edit.

Edited by dimchik

Share this post


Link to post
Share on other sites
zfisherdrums

Here is an alternative method suggested by the MSDN documentation:

CONST $adCmdText = 1, $adExecuteNoRecords = 128

Dim $oConn = ObjCreate( "ADODB.Connection" )
Dim $strSQL = "UPDATE table1 SET times_used = 5 WHERE id=4"

$oConn.Open( "Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\OfficeCDs.mdb" )
$oConn.Execute( $strSQL, "", $adCmdText + $adExecuteNoRecords )
$oConn.Close
$oConn = 0

Will this do the trick?

Zach...

Edited by zfisherdrums

Share this post


Link to post
Share on other sites
dimchik

This surely did the trick Thank you very much all for your help

Share this post


Link to post
Share on other sites
GEOSoft

This surely did the trick Thank you very much all for your help

If you are doing a lot of work with Access databases then take a look at this UDF

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


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

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  

×