handling glitches in sql string

I need to include glitches within a query string, but keep getting an "unterminated string" error.  How would I modify the below to accept glitches within the Name field?

Set conn = CreateObject("ADODB.Connection")
conn.Open MSSqlConnStr

MyName = "O'Brien"

MySql = "SELECT * FROM MyTable WHERE Name = '" & MyName & "'"

Set rs = conn.Execute(MySql)

If Not rs.EOF Then MsgBox("Found Name!")

Who is Participating?
dancebertConnect With a Mentor Commented:
Is your back end Sql Server?  From the documentation:

When using single quotation marks to delimit a character constant that
contains an embedded single quotation mark, use two single quotation marks
to represent the embedded single quotation mark.

For example,
MyName= replace(MyName, "'", "''")

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.