SQL Updates/Inserts with data with double quotes

Question

I have a database that collects client information using assessments and other tools. Often in the assessments there are double quotes in the data when - as may be expected - a direct quote is captured. When I run sql updates (Access front end using VBA to SQL Server 2008 R2 backend) it blows up on double quotes in the data. In the interim I've asked staff to use single quotes when they enter data, but that is an unsustainable solution as they forget and the program crashes when it hits the double quotes. The datatype is nvarchar(max).

The current VBA string looks like this:

strInsertSQL = "INSERT INTO tblIRPDetail(IRPID, SectionID, Challenge, Goal, Objective, Intervention, IntDate) VALUES(" & intNewID & ", " & intSection & ", """ & strChallenge & """, """ & strGoal & """, """ & strObjective & """, """ & strIntervention & """, """ & strIntDate & """);"

Essentially any of the strVariables could have single or double quotes in any combination. It works for single quotes but not doubles. Surely this is a fairly common issue, I'm hoping someone has a simple solution!

Thanks in advance!

Answer 1

An Access SQL statement can use either single or double quotes for text values. So you could use single quotes in your INSERT statement, but then you would run into a similar problem when the text to insert includes single quotes and which you could then resolve by doubling up the single quotes within the insert text:

Replace(strChallenge, "'", "''")

That doesn't seem like much of an improvement. OTOH, since you're using VBA to insert a row to your table, you don't need to use an INSERT query.

Open your table as a DAO.Recordset, add a new row, then assign your variable values to the corresponding fields.

Public Sub NPO_AddRow()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblIRPDetail", dbOpenTable, dbAppendOnly)
    With rs
        .AddNew
        !IRPID = intNewID
        !SectionID = intSection
        !Challenge = strChallenge
        !Goal = strGoal
        !Objective = strObjective
        !Intervention = strIntervention
        !IntDate = strIntDate
        .Update
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
End Sub

Note that, unlike a parameter query, this approach will allow you to add text strings longer than 255 characters.