Sign in to follow this  
Followers 0
Automania

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

7 posts in this topic

#1 ·  Posted (edited)

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 by Automania

Share this post


Link to post
Share on other sites



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.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by Automania

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by Automania

Share this post


Link to post
Share on other sites

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

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