Access VBA Insert INTo Error

Question

I have a listbox named lstAvailable and i am trying to send the values selected from it into a table. But i get an error saying syntax error in Insert Into statement. Machine Subsystem ID,NewComponents are of type integer and MO_TAG and Components_Detail is of text type

      Dim varItem As Variant
      Dim sSQL As String
      Dim nRid As Long 
     Dim nEid As Long
     Dim nEid1 As String
     Dim nEid2 As String 
   Dim varRow As Variant
     nRid = listMachineSubSystem.Column(1)
  For Each varRow In lstAvailable.ItemsSelected
    nEid = lstAvailable.Column(0, varRow)
    nEid1 = lstAvailable.Column(2, varRow)
    nEid2 = lstAvailable.Column(3, varRow)
    sSQL = "INSERT INTO tblComponents ([Machine Subsystem ID], [NewComponents], [MO_TAG], [Components_Detail])" _
    & " VALUES (" & nRid & ", " & nEid & ", " & nEid1 & ", " & nEid2 & ");"
    'Debug.Print "Insert " & sSQL
    CurrentDb.Execute sSQL, dbFailOnError

Textual values must be single quoted, however this causes SQL Injection problems, use a QueryDef E.g. https://stackoverflow.com/a/38660694/246342 — Alex K., Apr 11 '18 at 13:57

score 0 · Accepted Answer · answered Apr 11 '18 at 13:58

0

Maybe this will help?:

sSQL = "INSERT INTO tblComponents ([Machine Subsystem ID], [NewComponents], [MO_TAG], [Components_Detail])" _
& " VALUES (" & nRid & ", " & nEid & ", '" & nEid1 & "', '" & nEid2 & "');"

I added some ' ' where the Strings are in the value brackets.

And:

[Machine Subsystem ID]

Shouldn't this be only one word like Machine_Subsystem_ID?

answered Apr 11 '18 at 13:58

Matthias Neubert

275
1
5
24

I would add some simple injection protection like this: `Replace(nEid1,"'","''")` instead of just `nEid1` – SunKnight0 Apr 11 '18 at 20:13
Is it necessary to add the injection protection or can i just go ahead with the above answer as it is working fine – user9527824 Apr 11 '18 at 20:32
@user9527824 I would say it depends on which data you work with and who works with it. If the data aren't that sensitive and you trust the people who work witht this data I would say it isn't that necessary. Else it is recommended. And it is best practise to protect against injection. – Matthias Neubert Apr 12 '18 at 06:41

Access VBA Insert INTo Error

1 Answers1