asked on

sql statement problem

there is error in dbFA.Execute line
what the problem
i'm trying to copy one value of FTLQTY in fa_tmplocation table to another in another FULQTY in another fa_usrlocation table

sql = "SELECT * FROM fa_usrlocation,fa_tmplocation"
Set l_rs = dbFA.OpenRecordset(sql, dbOpenDynaset)
dbFA.Execute "Update fa_usrlocation Set FULQTY = FTLQTY FROM fa_tmplocation WHERE FTLDEFCODE = '" & usr_defcode & "' AND FTLDEFGRP = '" & usr_defgrp & "' AND FULSTRLOC = FTLSTRLOC AND FULEDLOC = FTLEDLOC"

Set wrkODBC = DBEngine.CreateWorkspace("NWorkspace", "admin", "", dbUseODBC)
wrkODBC.DefaultCursorDriver = dbUseODBCCursor
Set dbFA = wrkODBC.OpenDatabase("IS", _
dbDriverNoPrompt, False, _
"ODBC;DATABASE=;UID=a1;PWD=a2;DSN=Oracle")

viktornet

sql = "SELECT * FROM fa_usrlocation,fa_tmplocation"

should read

sql = "SELECT * FROM fa_usrlocation,fa_tmplocation;"

there must be a semicolon at the end of the SQL statment...

..-=ViKtOr=-..

kheng

ASKER

it's the same
still not working

MikeP090797

Try putting the table name before the fields:
dbFA.Execute "Update fa_usrlocation Set fa_usrlocation.FULQTY = fa_tmplocation.FTLQTY FROM fa_tmplocation WHERE fa_tmp.locationFTLDEFCODE = '" & usr_defcode & "' AND FTLDEFGRP = '" & usr_defgrp & "' AND FULSTRLOC = FTLSTRLOC AND FULEDLOC = FTLEDLOC"

What exactly are you trying to acheive here? This can go better in a single query I think

ASKER CERTIFIED SOLUTION

DavidMartin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial