The ' character is used in SQL to mark the start or end a string, so the following fails:
This is because the database engine thinks that you want the string to end after the letter O.
Where surname = 'O'Brien'
To put the ' character into an SQL string you need to use twice as many, so in this case we would add one extra, eg:
Where surname = 'O''Brien'
If you are getting the data from any source that could contain the ' character (such as input from your users) you should always use the Replace function to format the data, eg:
Dim mySQL as String
mySQL = "Select * From myTable Where surname = '" & Replace(Text1.text, "'","''") & "'"
Note however that the ' character is not the only issue, so doing this has only been a partial fix to the problem. A better solution (which deals with all of the other issues too) is to use Parameters.
For an explanation of the issues they solve and the code needed, see the FAQ article Why should I use Parameters instead of putting values into my SQL string?