Syntax of Vars in VBA with SQL

Question

in my Job I have a Little Problem with VBA (I'am new to VBA, so it is maybe only a Little fault, but I haven't found a solution in the Internet)

    For i = 0 To Me!Liste_Eingangsdiagnostik.ListCount - 1
    diagName = Me!Liste_Eingangsdiagnostik.column(0, i) & "_" & Me!Liste_Eingangsdiagnostik.column(1, i) & "_" & 1


    If Me!Liste_Eingangsdiagnostik.Selected(i) Then
    buildFilter_EingangsDiag i
    tableName = Me!Liste_Eingangsdiagnostik.column(0, i)
    Debug.Print tableName
    newColumn = "ALTER TABLE " & tempTable & " ADD COLUMN " & tableName & " " & "Long"
    Set rs = CurrentDb.OpenRecordset("abf_Export_Diagnostik") 'Erstelle die Antworten

    'updateExport = "INSERT INTO Export_Hilfstabelle " & " (IDTAB_EP, ID_Antwortbogen, tableName) " & " (rs.Fields(1).Value,rs.Fields(1).Value,.Fields(1).Value)"

    db.Execute newColumn

        Do While Not rs.EOF
        a = rs.Fields("IDTAB_EP").value
        Debug.Print a
        b = rs.Fields("ID_Antwortbogen").value
        Debug.Print b
        c = rs.Fields("strAntworten_Text").value
        Debug.Print c

        db.Execute "INSERT INTO [Export_Hilfstabelle] " & " ([IDTAB_EP], [ID_Antwortbogen], [ID_Frage], ['" & tableName & "']) VALUES " & " ('" & a & "', '" & b & "',0,'" & c & "')"
        rs.MoveNext
        Loop

    End If
Next i

The Users of this Database want all their Diagnostics they choose in one Table. So I try to add for every Diagnostic a new column (tableName), but I got the ErrorLog: the field Name ist not correct. (I'm from Germany, so some Vars are in German.) Thank you very much for yout help!

Which line do you get that error on? the `ALTER TABLE` or the `INSERT INTO`? — Dave, Jun 20 '16 at 09:46
In the INSERT statement, the apostrophes in `['" & tableName & "']` are wrong, remove them. But that's probably not the only problem. -- See also: [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) — Andre, Jun 20 '16 at 09:55

score 0 · Accepted Answer · answered Jun 20 '16 at 10:55

The columns list in the INSERT statement must not have apostrophes.

db.Execute "INSERT INTO [Export_Hilfstabelle] " & 
  " ([IDTAB_EP], [ID_Antwortbogen], [ID_Frage], ['" & tableName & "']) 
  VALUES " & " ('" & a & "', '" & b & "',0,'" & c & "')"

should be

db.Execute "INSERT INTO [Export_Hilfstabelle] " & 
  " ([IDTAB_EP], [ID_Antwortbogen], [ID_Frage], [" & tableName & "]) 
  VALUES " & " ('" & a & "', '" & b & "',0,'" & c & "')"

Syntax of Vars in VBA with SQL

1 Answers1