SQL Problem with using UPDATE query in Access

[cantene]

Oh, Dear Friends,

I have a problem in using DoCmd.RunSQL again. This time I wish to update the old data in one table from another newly added table that contains fresh data.

My code is:
sSQL = "UPDATE Stock SET " & _
"Name=" & MyTableName & ".Name " & _
"FROM " & MyTableName & " " & _
"WHERE Stock.Ticker=" & MyTableName & ".Ticker"

' Run the SQL on the Access mdb from my program
accApp.DoCmd.RunSQL sSQL


My SQL statement is (when debug.print):

UPDATE Stock SET Name=TempTable.Name FROM TempTable WHERE Stock.Ticker=TempTable.Ticker

What's wrong with it? Can u help me to point this out? Thanks a lot.

[Parasu Raman]

I wont abit more explanation. According to the way I see the question, u want to update STOCK table with the contents of the TempTable.
If my understanding is correct, u can't directly use another table's values. What u can do is store the value of the other table in a variable and then use that variable for updation.
Any probs, inform us

Cheers
Parasuraman

[cantene]

Originally posted by Parasu Raman
I wont abit more explanation. According to the way I see the question, u want to update STOCK table with the contents of the TempTable.
If my understanding is correct, u can't directly use another table's values. What u can do is store the value of the other table in a variable and then use that variable for updation.
Any probs, inform us

Cheers
Parasuraman

Dear Parasuraman,
Thanks for oyur kindly reply. But I have forgot to say when I run this SQL, the VB prompts an error message:

Sysntax error(MIssing Operator) in query expression 'TempTable.Name FROM TempTable'

So is it the ACCESS can't recognize the syntax of Table.Field? Can you write the correct SQL for me if according to yrour mentioned method?

[JamesM]

Easy way to get the correct SQL string for Access: create the query in the QBE grid, look at the SQL it creates, copy it into your VB project, substitute in any variables as required and you are done.

[Parasu Raman]

JamesM,
I havent heard of the QBE Grid. Can u pls show light on it and I could'nt find it in the components list.
Parasuraman

[Parasu Raman]

Cantene,
here is a short form code. Try this out...

- Open two recordsets.
- one (rs) for temptable and two (rs1) for stock
-
do while not rs.eof
do while not rs1.eof
if rs!ticker = rs1!ticker then
ssql = "update stock set name = '" & rs!name & "' where ticker = '" & rs!ticker & "'"
end if
rs1.movenext
loop
rs.movenext
loop

'update the command with the ssql value
rs.close
rs1.close


Any prbs??? Let me know

Parasu

[JamesM]

Parasu Raman,

QBE is only found within Access not in VB. It is a graphical way of constructing SQL statements kinda like creating views in SQL server.

James

[Parasu Raman]

Oh James,
I got puzzled. Thanx for that. Ive seen that in Access.

Good day
Parasu