VBA SQL Update with apostrophe in Text Field

pingme89

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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
369
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
1,952
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
139
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
139
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,114,207
Messages
5,546,546
Members
410,745
Latest member
citrictango
Top