0

I'm running an Insert Into SQL query from VBA, and I recently came across the string 'Women's Health / Sexual Health', which contains the ' character, and thus the query failed because ' is a text delimiter. This is how I get the text for the query:

Function getSqlInsertIntoQuery(arrHeaders, arrValues, tableIndex As enumTables)
    Dim dHeadersAndValues As New scripting.Dictionary
    Dim TableName As String

    TableName = getTableName(tableIndex)

    getSqlInsertIntoQuery = "INSERT INTO " & TableName & " ( [" & _
         Join(arrHeaders, "]," & vbNewLine & "[") & "])" & _
        " VALUES( " & Chr(39) & Join(arrValues, Chr(39) & "," & Chr(39)) & Chr(39) & ") ;"

    Debug.Print getSqlInsertIntoQuery
End Function

Can anyone tell me of a way to clean the text of special characters that I should escape? Or is there a list of characters that I can replace using regex?

Improve this question
4
  • 1
    Are you using ADO? Use ADODB.Parameter - no more hassles with formatting any data. Commented Sep 23, 2021 at 14:09
  • @FunThomas thanks for the idea, can you directly to an example or use case of how to use it? Google gives me unrelated answers because "Parameter" is widely used. Commented Sep 23, 2021 at 14:46
  • Here's an example of using parameters - stackoverflow.com/questions/40437480/…. You'd need to do a little more work to build the SQL though, since you have a variable list of fields/values. Commented Sep 23, 2021 at 15:24
  • They both answered your question; another reason to parametrize is the serious issue of SQL injection: here an answer that explains and avoid it, even for DAO Commented Sep 23, 2021 at 17:00
Related questions
0
sql Query in VBA code
3
Run SQL Statement in Access using VBA code
0
SQL query performed in VBA
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.