MS Access sql - Update query syntax

Question

Seems a small issue with an sql update query. But I can not get my head around this issue. Not very much acquainted with sql.

Based on a selection, selected rows from a table (7DARphases) are copied and inserted to another table (7tblDAR). And the Project ID (PRJID column) for all the inserted rows should be updated with a fixed value (PID) taken from the form. Issue is: I am facing syntax issue in the part where the fixed value needs to be inserted in the PRJID column of added rows.

Code is:

Set dbs = CurrentDb

PID = Me.PRJID.Value

sqlstr = "INSERT INTO 7tblDAR (Phase, Deliverable, Link_PIM_temp, Link_PIM_WB, Description, Accept_criteria, Deliv_type, TollgateID, Workstream, Accountable, ClientRACI) SELECT [7DARphases].Phase, [7DARphases].Deliverable, [7DARphases].Link_PIM_temp, [7DARphases].Link_PIM_WB, [7DARphases].Description, [7DARphases].Accept_criteria, [7DARphases].Deliv_type, [7DARphases].TollgateID, [7DARphases].Workstream, [7DARphases].Accountable, [7DARphases].ClientRACI FROM 7DARphases WHERE ((([7DARphases].SolType) Like '*2PP*')) ORDER BY [7DARphases].Phase, [7DARphases].Deliverable;"

sqlUpdt = "update 7tblDAR set PRJID =  '" & Me.PRJID.Value & "' from 7tblDAR where tblDAR.PRJID = """""


dbs.Execute sqlstr, dbFailOnError

dbs.Execute sqlUpdt, dbFailOnError

The 'sqlstr' works fine and inserts rows. But 'sqlUpdt' gives error:
"Run-time error 3075: Syntax error (missing operator) in query expression ..."

Can you please help me out with this.

Plus, if possible, can you suggest to perform this action in one query itself.

Gordon Linoff · Accepted Answer · 2017-02-25 12:39:23Z

0

Why not put the value in when you insert the values?

sqlstr = "INSERT INTO 7tblDAR (Phase, Deliverable, Link_PIM_temp, Link_PIM_WB, Description, Accept_criteria, Deliv_type, TollgateID, Workstream, Accountable, ClientRACI, PRJID)
    SELECT . . .,
           '" & Me.PRJID.Value & "' 
    WHERE [7DARphases].SolType Like '*2PP*')
    ORDER BY [7DARphases].Phase, [7DARphases].Deliverable;"

This saves the trouble of having to update it later.

answered Feb 25, 2017 at 12:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kashyap Kumar Over a year ago

Perfecttt solution Gordon...Many many thanks for this solution

Collectives™ on Stack Overflow

MS Access sql - Update query syntax

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related