VBA SQL Update with apostrophe in Text Field

[pingme89]

Using the code below, everything works well until one of the CompanyName values have an apostrophe in it.
VBA generates an error.

For Example: CompanyName = "John's Plumbing Supplies"
How can I modify the code to be able to update the CompanyName Field in Access when the string has an apostrophe in it?

I am using the following code:

Public Sub QUpdateCompanyName()
Dim connDB As New ADODB.Connection
Dim adoRecSet As New ADODB.Recordset
Dim sSQL As String

TotalRow = ThisWorkbook.Worksheets("SQLQuoteLogStaging").Cells(Rows.count, 81).End(xlUp).Row
'your data source with which to establish connection - MS Access Database Name:
strDBName = "CCcalc.accdb"
'get path / location of the database, presumed to be in the same location as the host workbook:
strMyPath = "C:\Users\phili\Dropbox\"
'set the string variable to the Database:
strDB = strMyPath & strDBName
connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDB
TableName = "QuoteLog"

For i = 2 To TotalRow

        CustID = ThisWorkbook.Worksheets("SQLQuoteLogStaging").Range("CC" & i).Value
        CompanyName = ThisWorkbook.Worksheets("SQLQuoteLogStaging").Range("CD" & i).Value
        sSQL = "UPDATE " & TableName & " SET CompanyName ='" & CompanyName & "' " & " WHERE CustID='" & CustID & "'"
        'Performs the actual query
        connDB.Execute (sSQL)


Next i

End Sub

 

[JonXL]

Use two, which you can get with replace:

CompanyName = Replace(CompanyName, "'", "''")

There are other ways, too. And there are other considerations (look up SQL injection). But as the minimum of what you're trying to do, that should get you there.

 

[Micron]

Not sure, but I think this will work too. Replace("someText"," ' ", chr(39))
Note that I injected spaces around the ' for clarity. You would not include the spaces in your code.
I seem to recall that worked for me once, but I wouldn't know where to look for it. Apologies if it doesn't.

 

[pingme89]

Use two, which you can get with replace:

CompanyName = Replace(CompanyName, "'", "''")

There are other ways, too. And there are other considerations (look up SQL injection). But as the minimum of what you're trying to do, that should get you there.

Thank you. That seem to have done the job.

 

[pingme89]

Not sure, but I think this will work too. Replace("someText"," ' ", chr(39))
Note that I injected spaces around the ' for clarity. You would not include the spaces in your code.
I seem to recall that worked for me once, but I wouldn't know where to look for it. Apologies if it doesn't.

Thank you.