update mdb via recordset

[Tany]

Hi,

I do not think con.execute is ok to replace $rst.edit/update when browsing the recordset.

Any suggestion ?

Thanks.

T

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)

dim $Src

dim $SrcPage

Do

$Src = $rst.fields("Src").value

$SrcPage = $rst.fields("SrcPage").value

;calc OpeningBell (or NextFirstConnect). On Week-end, calc OpeningBell of last friday

;calc ClosingBell. On Week-end, calc ClosingBell of last friday

;calc ClosingBellLastPossibleConnect

;calc NextOpeningBell (or NextFirstConnect)

;calc NextConnect $tmpNC

; $rst.Edit

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

; $rst.Update

$con.Execute("UPDATE Sources_Pages SET PollNextConnTS = " & $tmpNC & " WHERE Src = '" & $Src & "' AND SrcPage = '" & $SrcPage & "'")

$rst.MoveNext

until $rst.eof

$rst.Close

$con.Close

[PsaltyDS]

Put literal single quotes around all the data values:

$con.Execute("UPDATE Sources_Pages SET PollNextConnTS = '" & $tmpNC & "' WHERE Src = '" & $Src & "' AND SrcPage = '" & $SrcPage & "'")

You had them around $Src and $SrcPage, but not around $tmpNC.

[Tany]

Put literal single quotes around all the data values:

$con.Execute("UPDATE Sources_Pages SET PollNextConnTS = '" & $tmpNC & "' WHERE Src = '" & $Src & "' AND SrcPage = '" & $SrcPage & "'")

You had them around $Src and $SrcPage, but not around $tmpNC.

Hi,

Yes, I forgot them. Slip of the finger.

I noticed as well that the "VBA-style" working with $rst.fields(etc..) works when $rst.edit and $rst.update are not set.

Other issue that I have in the conversion is the alternative way for index, seek and nomatch

$rstST.Index = "SrcTickerDsc"

$rstST.Seek "=", $rstAQ!Src, $tmpSrcTickerDsc

If $rstST.NoMatch Then

Any suggestion ?

Is there any paper on working with mdb, or converting to mdb ?

Now I run from one to an other problem.

Like, why is ".OpenRecordSet" not supported, though standard syntax for VBA

Thanks.

T

[PsaltyDS]

Is this still an AutoIt question, or are you looking for help in something else? This is not AutoIt sytax:

$rstST.Seek "=", $rstAQ!Src, $tmpSrcTickerDsc

The .seek ADO method should be available IF the ADO provider supports it, but in AutoIt it looks more like:

Global $avSearchTerms[2] = ["Apples", "Oranges"]
Global Const $asSeekFirstEQ = 1
$RS.Seek($avSearchTerms, $adSeekFirstEQ)
If $RS.EOF Then
     MsgBox(16, "No Match", "No match")
Else
     $sMatch = $RS.Fields("Fruit").Value
     MsgBox(64, "Match", "Got match = " & $sMatch)
EndIf

Beyond that, I've completely lost the bubble on what your AutoIt question is.

Edited February 22, 2010 by PsaltyDS

[Tany]

Is this still an AutoIt question, or are you looking for help in something else? This is not AutoIt sytax: The .seek ADO method should be available IF the ADO provider supports it, but in AutoIt it looks more like:

Global $avSearchTerms[2] = ["Apples", "Oranges"]
Global Const $asSeekFirstEQ = 1
$RS.Seek($avSearchTerms, $adSeekFirstEQ)
If $RS.EOF Then
     MsgBox(16, "No Match", "No match")
Else
     $sMatch = $RS.Fields("Fruit").Value
     MsgBox(64, "Match", "Got match = " & $sMatch)
EndIf

Beyond that, I've completely lost the bubble on what your AutoIt question is.

Thank you for reply.

$RS.Seek($avSearchTerms, $adSeekFirstEQ) is what I learned on the WEB for "Microsoft.Jet.OLEDB.4.0", but I found in AutoIt-forum that for mdb it is suggested to work with "ADODB" and there the syntax is $RS.seek "=", $avSearchTerms which requires $RS.Index to be set. At least in VBA !

My bubble is... get the seek work in AutoIt. And now it runs in...

C:\SB\ai_UpdQuotes.au3(85,28) : ERROR: syntax error

$rstST.Seek "="

for...

Dim $dB = ObjCreate("ADODB.Connection")

$dB.Open("Driver={Microsoft Access Driver (*.mdb)};DBQ=" & $MyPath & $MydB)

Dim $rstST = ObjCreate("ADODB.Recordset")

$rstST.CursorType = 1

$rstST.LockType = 3

$rstST.Open("SELECT * FROM Sources_Tickers", $dB)

$rstST.Index = "SrcTickerDsc" ;Src, SrcTickerDsc

etc, etc

...

$rstST.Seek "=", $rstAQ.Fields("Src").Value, $tmpSrcTickerDsc

If $rstST.NoMatch Then

etc, etc

...

I already replaced the seek by...

$rstST.Seek($rstAQ.Fields("Src").Value & $tmpSrcTickerDsc, 1)

then I get...

hmmm... no ERROR any more !

As you say... prob provider stuff. Guess I am too much biased by VBA.

Cheers & thanks for the patience,

T

PS : is there a way to test au3-code step-by-step ?

[jchd]

PS : is there a way to test au3-code step-by-step ?

Look at some listed here.