Sign in to follow this  
Followers 0
Tany

update mdb via recordset

6 posts in this topic

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

Share this post


Link to post
Share on other sites



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.

:mellow:


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

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.

:mellow:

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

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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.

:mellow:

Edited by PsaltyDS

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

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.

:mellow:

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 ?

Share this post


Link to post
Share on other sites

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

Look at some listed here.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

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