Sign in to follow this  
Followers 0
skippynz

updating MS Access Database

9 posts in this topic

#1 ·  Posted (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 by craig.gill

Share this post


Link to post
Share on other sites



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.

:P


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)

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

Share this post


Link to post
Share on other sites

Add a COM error handler to give you the details of what went wrong. See the help file under OBJ/COM Reference.

:P

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#6 ·  Posted (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 by gms002

Share this post


Link to post
Share on other sites

i cant use the date formatted field in access

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

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