Syntax error (missing operator) in query expression in VBA in MS Access

Question

I get the following error message: 3075 missing operator in query expression

here a short snippet of the code:

Dim dbs As Database
Set dbs = CurrentDb()
dbs.Execute "DELETE FROM TempTable_Entity_mapping_DEFSET_Selection;"
Do While Not rsDEFSET.EOF
    Debug.Print rsDEFSET.Fields.item("RESULT").Value
    MIP = rsDEFSET.Fields.item("FK_DIM_MBR_ITEM").Value
    KPIName = rsDEFSET.Fields.item("SHORTNAME_MBR").Value
    ID = rsDEFSET.Fields.item("RESULT").Value
    DefSetName = rsDEFSET.Fields.item("KPI_DEFSET_NAME")
    Scenarios = rsDEFSET.Fields.item("SCENARIOS")

    fillTempTab = "INSERT INTO TempTable_Entity_mapping_DEFSET_Selection (MIP, KPIName, ID, DefSetName, Scenarios) VALUES ('" & MIP & "','" & KPIName & "','" & ID & "','" & DefSetName & "','" & Scenarios & "');"
    Debug.Print fillTempTab
    dbs.Execute fillTempTab
    rsDEFSET.MoveNext
Loop

You should be using prepared statements; especially if you are getting input from an untrusted source. Here's an example of how to use them: stackoverflow.com/questions/6572448/… — mechanical_meat
I agree with @bernie, you can store your insert and delete statements in persistent querydefs, then add parameters to the querydef object. Also, you don't need the '.Fields.item,' rsDefset("field") is fine. — Beth
you get the error on the dbs.execute statement? also, the ID field is text and not numeric? — Beth
Instead of doing this all through VBA, couldn't you just create an INSERT INTO query and post your results directly into TempTable_Entity_mapping_DEFSET_Selection? Also would like to echo Ken's comment: where is rsDEFSET defined/assigned? Is this from an external db? — Gaffi

RosenkranzC RosenkranzC · Accepted Answer · 2013-02-21T20:56:33

Looks like you're missing what you want to delete in your SQL statement

Your first query should read

DELETE * FROM TempTable_Entity_mapping_DEFSET_Selection;

Syntax error (missing operator) in query expression in VBA in MS Access

1 Answers