0

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
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
matherte
  • 1
  • 3
  • 1
    Where is `rsDEFSET` defined? – Ken Keenan Feb 13 '13 at 19:05
  • 2
    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: http://stackoverflow.com/questions/6572448/msaccess-prepared-statements – mechanical_meat Feb 13 '13 at 19:08
  • 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 Feb 13 '13 at 19:14
  • you get the error on the dbs.execute statement? also, the ID field is text and not numeric? – Beth Feb 13 '13 at 19:16
  • I also wonder if KPIName (or any of the fields) contains a single quote or comma. what is the value of fillTempTab? – Beth Feb 13 '13 at 19:18
  • Unfortunately that last edit change the code to something that will not compile and disguised any real errors. I have rolled back. – Fionnuala Feb 13 '13 at 20:15
  • 1
    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 Feb 13 '13 at 21:15
  • rsDefset is defined a couple of lines above as `Set rsDEFSET = objCmd.Execute` and `Dim rsDEFSET As New ADODB.Recordset` – matherte Feb 14 '13 at 07:37
  • yes, error is on dbs.execute – matherte Feb 14 '13 at 09:08

1 Answers1

0

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;
RosenkranzC
  • 45
  • 1
  • 7