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

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?

CodePudding user response：

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.

CodePudding user response：

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