Help Updating Access DB record

[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

[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 November 19, 2007 by zfisherdrums

[Klaatu]

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

[dimchik]

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

[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...

[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 November 19, 2007 by dimchik

[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

[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 November 19, 2007 by dimchik

[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 November 20, 2007 by zfisherdrums

[dimchik]

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

[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