updating MS Access Database

[skippynz]

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

[PsaltyDS]

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.

[Spiff59]

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

[skippynz]

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 message

we intercepted a com error

number is: 80020009

windescription is: HH (however these H's are rotated 90 degrees so sitting above each other)

thats all i get !! urrrggghhhh

[skippynz]

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.

[gms002]

;

;

; 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

[Spiff59]

i cant use the date formatted field in access

Figured that one out yet? Or, care to elaborate?

[skippynz]

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.

[Spiff59]

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'"