MS Access VBA SQL Append query returns syntax error when appending string with special characters

Question

I am running Ms Access VBA Sql code to append selected items from listbox to a temp table. My code is working just fine however, when I pick certain items from listbox it returns the following error.

Runtime error '3075': Syntax error (missing operator) in query expression "Bachelor Of Technology (B.Tech/B.E) (Computers) Institue : Maeer's Mit College of Engineering'.

I think it has something to do with the special characters included or something.

Private Sub Command101_Click()
DoCmd.SetWarnings False
code1 = "INSERT INTO tmp_education_list ( education ) "
If Me.Frame97 = 1 Then

    For i = 0 To Me.List96.ListCount - 1
        sqlstr = code1 + "VALUES('" & Me.List96.ItemData(i) & "');"
        DoCmd.RunSQL sqlstr
    Next I
End If
DoCmd.Requery "Text135"
DoCmd.SetWarnings True
End Sub

What am I missing here?

It's the single quote. Use my [CSql](https://stackoverflow.com/questions/43589800/syntax-error-in-insert-into-statement-whats-the-error/43591778#43591778) function. — Gustav, Oct 15 '22 at 12:29
Always use parameters over string concatenation! As Jeff Atwood reminds us [Give me parameterized SQL, or give me death](https://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/)! — Parfait, Oct 16 '22 at 02:38

score 2 · Answer 1 · answered Oct 15 '22 at 12:37

That's because the single quote in Maeer's breaks the SQL statement. You can create a temporary query and pass the value as parameter.

Const SQL As String = "PARAMETERS [pValue] Text (255); INSERT INTO tmp_education_list ( education ) SELECT [pValue] As [Value];"

Dim q As DAO.QueryDef
Set q = CurrentDb().CreateQueryDef("", SQL) 'No query name means it's temporary

'...

For I = 0 To List96.ListCount - 1
    q.Parameters("[pValue]").Value = List96.ItemData(I)
    q.Execute
    
Next I

q.Close

score 1 · Accepted Answer · answered Oct 15 '22 at 12:37

Use the power of DAO, and you won't see this:

Private Sub Command101_Click()

    Dim Records As DAO.Recordset
    
    If Me.Frame97 = 1 Then      
        Set Records = CurrentDb.OpenRecordset("Select education From tmp_education_list")
        For i = 0 To Me.List96.ListCount - 1                
            Records.AddNew
            Records(0).Value = Me.List96.ItemData(i)
            Records.Update
        Next I
        Records.Close
    End If
    
    DoCmd.Requery "Text135"

End Sub

Also, do rename your controls to something meaningful.

MS Access VBA SQL Append query returns syntax error when appending string with special characters

2 Answers2

Linked