MsAccess Vba SQL Quotes (single / double)

Question

I have a sql that works fine. In the where-clause I' am using a recordset as parameter.

Dim dbs As DAO.Database

Dim rsData As DAO.Recordset

Dim strsql As String

Set dbs = CurrentDb

Set rsData = dbs.OpenRecordset("Qry_GetEntPersoon")
sqltext = "SELECT KISS_tblENTITEITEN.EntiteitVatting FROM KISS_tblENTITEITEN WHERE ((KISS_tblENTITEITEN.EntiteitVatting)= '" & rsData!KISSEntiteitVatting & "');"

Set qrydeftemp = dbs.CreateQueryDef("xxtempxx", sqltext)

I have a problem when there are quotes in the name, f.e. M'HAMED, then I get a syntax error.
I don't know how to change the single quotes to double quotes in the sql to correct the syntax error.

It should be "M'HAMED" for the query to run without a syntax error.

Answer 1

For such situations it is recommended to use Parameterised Queries using Parameters property of qrydeftemp. Some examples can be found in documentation and articles.

You can also try an intermediate fix:

sqltext = "SELECT . . . '" & Replace(rsData!KISSEntiteitVatting, "'", "''") & "');"

But you should consider the parameters instead of such concatenation.