ADO Access Query bombs when data has apostrophe

[Ken Valenti]

This code works, but when EmployeeName has an appostophe , rs.open bombs.
(ADO seems to use an apostrophen instead of quotes to deliniate text)
Is there a workaround?

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Sub TestADO(EmployeeName As String)
Set cn = New ADODB.Connection
cn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & DBaseName &
";Uid=Admin;Pwd=;"
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM YTD_Data WHERE ProjectManager='" & EmployeeName &
"';", cn, adOpenDynamic, adLockOptimistic, adCmdText
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

 

[Vergel Adriano]

Ken,

try:

rs.Open "SELECT * FROM YTD_Data WHERE ProjectManager='" &
Replace(EmployeeName, "'", "''") & "';", cn, adOpenDynamic, adLockOptimistic,
adCmdText