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?

Always use parameters over string concatenation! As Jeff Atwood reminds us Give me parameterized SQL, or give me death! — Parfait
– Parfait, Commented Oct 16, 2022 at 2:38

Kostas K. · Accepted Answer · 2022-10-15 12:37:40Z

2

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

answered Oct 15, 2022 at 12:37

Kostas K.

8,5282 gold badges26 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gustav · Accepted Answer · 2022-10-15 12:37:18Z

1

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.

answered Oct 15, 2022 at 12:37

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related