Stack overflow when replacing ' with '' in VB 6.0

Question

I'm looking into some legacy VB 6.0 code (an Access XP application) to solve a problem with a SQL statement by the Access app. I need to use replace single quotes with 2 single quotes for cases where a customer name has an apostrophe in the name (e.g. "Doctor's Surgery":

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

Which will escape the single quote, so I get the valid SQL:

SELECT blah FROM blah WHERE customer = 'Doctor''s Surgery'

Unfortunately the Replace function causes an infinite loop and stack overflow, presumably because it replace function recursively converts each added quote with another 2 quotes. E.g. one quote is replaced by two, then that second quote is also replaced by two, and so on...

----------------EDIT---------------

I have noticed (thanks to posters) that the replace function used in this project is custom-written:

Public Function replace(ByVal StringToSearch As String, ByVal ToLookFor As String,
ByVal ToReplaceWith As String) As String
Dim found As Boolean
Dim position As Integer
Dim result As String

position = 0
position = InStr(StringToSearch, ToLookFor)
If position = 0 Then
    found = False
    replace = StringToSearch
    Exit Function
Else
    result = Left(StringToSearch, position - 1)
    result = result & ToReplaceWith
    result = result & Right(StringToSearch, Len(StringToSearch) - position - Len(ToLookFor) + 1)
    result = replace(result, ToLookFor, ToReplaceWith)
End If
replace = result

End Function

Apparently, VB didn't always have a replace function of it's own. This implementation must be flawed. An going to follow folk's advice and remove it in favour of VB 6's implementation - if this doesn't work, I will write my own which works. Thanks everyone for your input!

Answer 1

We use an VB6 application that has the option of replacing ' with ` or removing them completely.

You could also walk through the letters, building a second string and inserting each ' as ''.

Answer 2

I just tried this in Access and it works fine (no stackoverflow):

 Public Function ReplaceSingleQuote(tst As String) As String
        ReplaceSingleQuote = Replace(tst, "'", "''")
 End Function


 Public Sub TestReplaceSingleQuote()
        Debug.Print ReplaceSingleQuote("Doctor's Surgery")
 End Sub

Answer 3

Are you sure that it's not a proprietary implementation of the Replace function?

If so it can just be replaced by VB6's Replace.

I can't remember which version it appeared in (it wasn't in Vb3, but was in VB6) so if the original code base was vb3/4 it could be a hand coded version.

EDIT

I just saw your edit, I was Right!

Yes, you should be able to just remove that function, it'll then use the in build VB6 replace function.