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?

Dumitru Daniel
  • 571
  • 4
  • 19
  • 1
    Are you using ADO? Use `ADODB.Parameter` - no more hassles with formatting any data. – FunThomas Sep 23 '21 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. – Dumitru Daniel Sep 23 '21 at 14:46
  • Here's an example of using parameters - https://stackoverflow.com/questions/40437480/vba-insert-into-with-ado-parameters. You'd need to do a little more work to build the SQL though, since you have a variable list of fields/values. – Tim Williams Sep 23 '21 at 15:24
  • They both answered your question; another reason to parametrize is the serious issue of SQL injection: here an [answer](https://stackoverflow.com/a/50166053/2752308) that explains and avoid it, even for DAO – Marcelo Scofano Diniz Sep 23 '21 at 17:00

0 Answers0