skippynz Posted March 15, 2009 Share Posted March 15, 2009 (edited) Hi - i need a little help updating an access database from within Autoit. i have the following code which ive hacked together. $DBASENAME = 'data.mdb' Dim $CONN, $CONN1 Dim $SQL_CODE = "UPDATE tablename SET LastUsed='16032009000000' WHERE Filename='filename here'" $CONN = ObjCreate('ADODB.Connection') $CONN.Open('Driver={Microsoft Access Driver (*.mdb)};Dbq=' & $DBASENAME & ';') $RecordSet = ObjCreate('ADODB.Recordset') $RecordSet.Execute($SQL_CODE, $CONN) $RecordSet.Update $RecordSet.Close() $CONN.Close but i get this error message $RecordSet.Execute($SQL_CODE, $CONN) $RecordSet.Execute($SQL_CODE, $CONN)^ ERROR In my database i have 1 table called "tablename" and in there i have about 200 entries each have the data in the following column headings - Filename - Description - LastUsed - i want to be able to update the LastUsed field via the script - but obviously i have an error in my code. if anyone can point me in the right direction that would be appreciated. thanks Craig Edited March 16, 2009 by craig.gill Link to comment Share on other sites More sharing options...
PsaltyDS Posted March 16, 2009 Share Posted March 16, 2009 Hi - i need a little help updating an access database from within Autoit. i have the following code which ive hacked together. $DBASENAME = 'data.mdb' Dim $CONN, $CONN1 Dim $SQL_CODE = "UPDATE tablename SET LastUsed='16032009000000' WHERE Filename='filename here'" $CONN = ObjCreate('ADODB.Connection') $CONN.Open('Driver={Microsoft Access Driver (*.mdb)};Dbq=' & $DBASENAME & ';') $RecordSet = ObjCreate('ADODB.Recordset') $RecordSet.Execute($SQL_CODE, $CONN) $RecordSet.Update $RecordSet.Close() $CONN.Close but i get this error message $RecordSet.Execute($SQL_CODE, $CONN) $RecordSet.Execute($SQL_CODE, $CONN)^ ERROR In my database i have 1 table called "tablename" and in there i have about 200 entries each have the data in the following column headings - Filename - Description - LastUsed - i want to be able to update the LastUsed field via the script - but obviously i have an error in my code. if anyone can point me in the right direction that would be appreciated. thanks Craig Add a COM error handler to give you the details of what went wrong. See the help file under OBJ/COM Reference. 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 Link to comment Share on other sites More sharing options...
Spiff59 Posted March 16, 2009 Share Posted March 16, 2009 (edited) Did you figure it out yet? I don't think you need all the recordset stuff for an "update"... $oADO = ObjCreate("ADODB.Connection") $oADO.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=\\X-Ray\TigerView\Data\tvdata.mdb") If @error Then MsgBox(0, "ERROR", "Failed to connect to the database") Exit EndIf $SQL = "UPDATE Table SET Field = '" & $String & "' WHERE Key = " & $Numeric & ";" $oADO.Execute($SQL) If @error > 1 Then MsgBox(0, "SQL ERROR", "Error " & @error & ": " & $SQL) EndIf $oADO.Close I'm using basic error-handling, but Access seems to be well-behaved, so it's been sufficient. I didn't test if Access requires the semi-colon to terminate the SQL statement, am thinking so. I assume the ; at the end of your DBQ= string is ok, although I haven't bothered. Edit: To answer my own questions... The including or excluding of either semi-colon made no difference. The above works for me. Edited March 16, 2009 by Spiff59 Link to comment Share on other sites More sharing options...
skippynz Posted March 18, 2009 Author Share Posted March 18, 2009 Add a COM error handler to give you the details of what went wrong. See the help file under OBJ/COM Reference. ok did that and got the following messagewe intercepted a com errornumber is: 80020009windescription is: HH (however these H's are rotated 90 degrees so sitting above each other)thats all i get !! urrrggghhhh Link to comment Share on other sites More sharing options...
skippynz Posted March 18, 2009 Author Share Posted March 18, 2009 Did you figure it out yet? I don't think you need all the recordset stuff for an "update"... $oADO = ObjCreate("ADODB.Connection") $oADO.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=\\X-Ray\TigerView\Data\tvdata.mdb") If @error Then MsgBox(0, "ERROR", "Failed to connect to the database") Exit EndIf $SQL = "UPDATE Table SET Field = '" & $String & "' WHERE Key = " & $Numeric & ";" $oADO.Execute($SQL) If @error > 1 Then MsgBox(0, "SQL ERROR", "Error " & @error & ": " & $SQL) EndIf $oADO.Close I'm using basic error-handling, but Access seems to be well-behaved, so it's been sufficient. I didn't test if Access requires the semi-colon to terminate the SQL statement, am thinking so. I assume the ; at the end of your DBQ= string is ok, although I haven't bothered. Edit: To answer my own questions... The including or excluding of either semi-colon made no difference. The above works for me. that seems to have done the trick - although i cant use the date formatted field in access the text format works ok - thanks for your help. Link to comment Share on other sites More sharing options...
gms002 Posted March 19, 2009 Share Posted March 19, 2009 (edited) ; ; ; 1. Remember that you execute a SQL statement using the connection, NOT a recordset ; create the ADO connection ($oAccessADO = _adoAccessOpen($AccessDB)) ; and execute the SQL statement ; $ADOconnection.EXECUTE ($ANY_VALID_SQL_STATEMENT) (see the code below) ; ; If you prefer to use a recordset, yuo can update ONE record at the time, therefore you must first find the record you want to update, something like this: ; ; create the ADO connection ($oAccessADO = _adoAccessOpen($AccessDB)) ; create your recordset $rsAccess = ObjCreate("ADODB.Recordset") $SQL = "Select ColumToUpdate from MyTable where ID = 123" ; With $rsAccess .open($SQL, $oAccessADO) If .BOF * .EOF == 0 Then .fields(0).value = "THE NEW VALUE" EndIf .close EndWith ; ; ; ; 2. I found that the 'Microsoft.Jet.OLEDB.4.0; ' provider string work better ; ; Good luck ; ; ; Global Const $ProviderJET = 'Microsoft.Jet.OLEDB.4.0; ' Global $oAccessADO, $rsAccess, $AccessDB ; ; ; Dim $SQL ; $AccessDB = "D:\22\db6.mdb" $SQL = "Update t set x = 'DDD'" $oAccessADO = _adoAccessOpen($AccessDB) $oAccessADO.execute($SQL) Exit ; ; ; Func _adoAccessOpen($AccessDB) $oAccessADO = ObjCreate("ADODB.Connection") $oAccessADO.Provider = $ProviderJET $oAccessADO.Open($AccessDB) If @error Then MsgBox(0, "ERROR", "Cant open Access Database , handle = " & $oAccessADO) EndIf Return $oAccessADO EndFunc ;==>_adoAccessOpen Edited March 19, 2009 by gms002 Link to comment Share on other sites More sharing options...
Spiff59 Posted March 19, 2009 Share Posted March 19, 2009 i cant use the date formatted field in accessFigured that one out yet? Or, care to elaborate? Link to comment Share on other sites More sharing options...
skippynz Posted March 19, 2009 Author Share Posted March 19, 2009 Figured that one out yet? Or, care to elaborate?no didnt figure that out - so i just changed the field to make it text only - im ok with that - for some reason if its a date formatted field the update command just bombs out with com errors that make no sense at all. Link to comment Share on other sites More sharing options...
Spiff59 Posted March 19, 2009 Share Posted March 19, 2009 no didnt figure that out - so i just changed the field to make it text only - im ok with that - for some reason if its a date formatted field the update command just bombs out with com errors that make no sense at all.I think this is the format to update an Access date/time field: $NewDate = "03/18/2009" "UPDATE tablename SET LastUsed= CVDate($NewDate) WHERE Filename='filename here'" Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now