How to work with single quote in Word VBA SQL query?

Question

I get the client name from the drop-down list and use this value to query the Excel table, however the name can contain one quote (for example, Adam Meat). This breaks my application and how can I make a request with a variable containing one quote?

Private Sub cboCompany_Change() Dim customerName As String customerName = cboCompany.Value rsT.Open "SELECT Customer, Postcode, Address1, Address2, State, Country FROM Customers WHERE Customer = '" & customerName & "'", cn, adOpenStatic 

Answer 1

If you specify two single quotes '' , one of them comes out of the other and leads to a single, try replacing it as follows:

 customerName = Replace(customerName, "'", "''") 

Answer 2

This gives you widespread use for SQL injection attacks. I would recommend changing this to a parameterized query like this

 Dim cmd as NEW ADODB.Command With cmd .CommandText="SELECT foo from tblBar where foo=?" .Parameters.Append .CreateParameter("@foo", adVarChar, adParamInput, 50, "What ever you want") .ActiveConnection=dbCon .CommandType=adCmdText End With Set rst=cmd.execute