VBA SQL Update with apostrophe in Text Field

pingme89

Board Regular
Joined
Jan 23, 2014
Messages
149
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:

VBA 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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
444
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Use two, which you can get with replace:

VBA Code:
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.
 
Solution

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,058
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 23, 2014
Messages
149
Use two, which you can get with replace:

VBA Code:
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

Board Regular
Joined
Jan 23, 2014
Messages
149
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,620
Messages
5,625,910
Members
416,141
Latest member
Bartek9q

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top