[solved] How to update a record in MS Access database?

[Automania]

Hello,

Thanks to this great forum and its search function I managed to add records with AutoIt to an MS Access database (database.mdb in this case, 2003 format) by using these lines of code:

$FULL_MDB_FILE_NAME = @ScriptDir & "\database.mdb"
$SQL_CODE = "select * from Table1"
$CONN = ObjCreate("ADODB.Connection")
$CONN.Open('Driver={Microsoft Access Driver (*.mdb)};Dbq=' & $FULL_MDB_FILE_NAME & ';')
$RecordSet = ObjCreate("ADODB.Recordset")

$CONN.Execute("insert into Table1 (column1, column2, column3) values ('hello column1, hello column2, hello column3')")
$CONN.Close

My script adds records and their status is manually updated by me daily. Mainly I change a value in a column from "pending" to "done" as well as change the time value when the record was added to the time I updated the record. I figured AutoIt could do the same job.

So, how can I update a specific field within "column1"? I searched and searched and tried other code examples but that usually created error messages only or the code was not suitable for my issue (still new to all this object coding stuff).

If you need more information I'll be happy to post it. The Access UDF is sadly not helpful in this case (and partly bugged) and only allows to replace existing values, not suitable in certain cases.

Edited December 23, 2008 by Automania

[PsaltyDS]

Hello,

Thanks to this great forum and its search function I managed to add records with AutoIt to an MS Access database (database.mdb in this case, 2003 format) by using these lines of code:

$FULL_MDB_FILE_NAME = @ScriptDir & "\database.mdb"
$SQL_CODE = "select * from Table1"
$CONN = ObjCreate("ADODB.Connection")
$CONN.Open('Driver={Microsoft Access Driver (*.mdb)};Dbq=' & $FULL_MDB_FILE_NAME & ';')
$RecordSet = ObjCreate("ADODB.Recordset")

$CONN.Execute("insert into Table1 (column1, column2, column3) values ('hello column1, hello column2, hello column3')")
$CONN.Close

My script adds records and their status is manually updated by me daily. Mainly I change a value in a column from "pending" to "done" as well as change the time value when the record was added to the time I updated the record. I figured AutoIt could do the same job.

So, how can I update a specific field within "column1"? I searched and searched and tried other code examples but that usually created error messages only or the code was not suitable for my issue (still new to all this object coding stuff).

If you need more information I'll be happy to post it. The Access UDF is sadly not helpful in this case (and partly bugged) and only allows to replace existing values, not suitable in certain cases.

How about using SQL UPDATE instead of INSERT?

$CONN.Execute("UPDATE Table1 column1='changed it' WHERE column2='hello column2'")

Of course, you'll have specify which record in the table to update with some kind of WHERE operator as shown.

[Automania]

How about using SQL UPDATE instead of INSERT?

$CONN.Execute("UPDATE Table1 column1='changed it' WHERE column2='hello column2'")

Of course, you'll have specify which record in the table to update with some kind of WHERE operator as shown.

Thank you, I hoped it would be rather simple and it looks like it is.

However, this does not work somehow, I must have some error somewhere. I used the following code for updating the record:

$dbpath1 = fileGetShortName(@ScriptDir) & "\database.mdb"

$FULL_MDB_FILE_NAME = $dbpath1
$SQL_CODE = "select * from Table1"
$CONN = ObjCreate("ADODB.Connection")
$CONN.Open('Driver={Microsoft Access Driver (*.mdb)};Dbq=' & $FULL_MDB_FILE_NAME & ';')

$RecordSet = ObjCreate("ADODB.Recordset")
$CONN.Execute("UPDATE Table1 column1='changed it' WHERE column2='hello column2'")
$CONN.Close

and all I get is

==> The requested action with this object has failed.:
$CONN.Execute("UPDATE Table1 column1='changed it' WHERE column2='hello column2'")
$CONN.Execute("UPDATE Table1 column1='changed it' WHERE column2='hello column2'")^ ERROR

Both columns exist in the mdb file and are exactly named like that.

Edited December 13, 2008 by Automania

[litlmike]

Thank you, I hoped it would be rather simple and it looks like it is.

However, this does not work somehow, I must have some error somewhere. I used the following code for updating the record:

$dbpath1 = fileGetShortName(@ScriptDir) & "\database.mdb"

$FULL_MDB_FILE_NAME = $dbpath1
$SQL_CODE = "select * from Table1"
$CONN = ObjCreate("ADODB.Connection")
$CONN.Open('Driver={Microsoft Access Driver (*.mdb)};Dbq=' & $FULL_MDB_FILE_NAME & ';')

$RecordSet = ObjCreate("ADODB.Recordset")
$CONN.Execute("UPDATE Table1 column1='changed it' WHERE column2='hello column2'")
$CONN.Close

and all I get is

==> The requested action with this object has failed.:
$CONN.Execute("UPDATE Table1 column1='changed it' WHERE column2='hello column2'")
$CONN.Execute("UPDATE Table1 column1='changed it' WHERE column2='hello column2'")^ ERROR

Both columns exist in the mdb file and are exactly named like that.

To add:

$CONN.Execute("INSERT into Table1 (Quote, Author, Counter) values ('hello', 'hello2', '2')")

To Update

$CONN.Execute("UPDATE Table1 SET column1= changed it WHERE column2=hello column2")

Not sure if you need the "'" after the =, I guess that depends if you need quotes.

[Automania]

Sorry for the really late reply. I must have had some error somewhere, litlmike found the error.

I got it to work a few days ago thanks to your help, guys. Here's the complete code for anyone stumbling across this via search:

$FULL_MDB_FILE_NAME = @ScriptDir & "\database.mdb"
$SQL_CODE = "select * from Table1"
$CONN = ObjCreate("ADODB.Connection")
$CONN.Open('Driver={Microsoft Access Driver (*.mdb)};Dbq=' & $FULL_MDB_FILE_NAME & ';')
$RecordSet = ObjCreate("ADODB.Recordset")

$conn.Execute("UPDATE Table1 SET Column1 = 'hello' WHERE Column2 = 'world'")

$CONN.Close

In this case the value will be changed in each field to "hello" if column2 contains "world" in the row. I noticed the code line even accepts multiple conditions if you want to update only specific rows:

WHERE Column2 = 'world' AND WHERE Column3 = 'and everyone else'

Thank you very much, everyone!

Edited December 23, 2008 by Automania

[Tany]

Hi,

Is there an equivalent for... $rst.edit

dim $con = ObjCreate("ADODB.Connection")

$con.Open("Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\SB\SB.mdb")

dim $rst = ObjCreate("ADODB.Recordset")

$rst.CursorType = 1

$rst.LockType = 3

$rst.Open("SELECT * FROM Sources_Pages WHERE SrcXLSActive = True ORDER BY Src", $con)

do

a lot of things

$rst.Edit

$rst.fields("PollNextConnTS").value = $tmpNC

$rst.Update

$rst.MoveNext

until $rst.eof

In all suggestions I only see : $conn.execute(update...

I am not sure if I just can replace the 3 lines between edit-update

Cheers,

T

[PsaltyDS]

The RS.Edit() is not available in an ADO Recordset. If you use the ADO object, you have to stick with its methods.