Escaping unwanted characters, mainly single quotes --replace function and implementation

Question

I was just testing my database and I realized that I run into problems wherever a text entry in my database contains a ' character (single quote). My solution for now is that before any .execute operations on a string, I call escape(string, "'", " "'" ").

Summarized example below:

qr = "INSERT INTO tblExample VALUES ( " & "'" & me.testparam & "'" & ");"
qr = Replace(qr, "'", " "'" ")
db.execute qr
'also tried  qr = "INSERT INTO tblExample VALUES ( " & "'" & replace(me.testparam,"'"," ") & "'" & ");"

This was what I assumed to be the correct workaround to prevent errors from values such as Tourette's.

There's two problems with this. First of all, it's not working. Second, I have over 50 locations in code throughout my app where I call the statement db.execute qr where qr is a string that could potentially contain a single quote. I need the field in the table to contain the single quote, so I can't just replace it with a space or something similar.

Two part question:

1. Is there a better solution than going through all of my code calling Replace on every string that is to be executed as a query?

2. Why is my current implementation failing? - I still get syntax error in query expression even when escaping the single quote to a space.

Answer 1

First examine these 2 lines.

"VALUES ( " & "'" & me.testparam & "'" & ");"
"VALUES ( '" & me.testparam & "');"

Both will produce the exact same string. The difference for me is that my brain comprehends the second version faster.

Now, here is what the comments are telling you to do ... replace each single quote in your source string with two single quotes. I added Debug.Print so you can view the finished string in the Immediate window (go there with Ctrl+g) ... you can then see the actual string rather than trying to imagine what it looks like.

qr = "INSERT INTO tblExample VALUES ( '" & _
    Replace(Me.testparam, "'", "''" & "');"
Debug.Print qr
db.Execute qr, dbFailOnError 

Since I assumed db is a DAO.Database object variable, I included the dbFailOnError option. You should include an error handler in your code to deal with any problems dbFailOnError exposes.

When you run into trouble with a VBA function in a query, drop to the Immediate window and test your function expression there. This one triggers a compile error, "Expected: list separator or )":

? Replace("Tourette's", "'", " "'" ")

But this one works:

? Replace("Tourette's", "'", "''")
Tourette''s

I mentioned that because it's useful in general, and also because your title starts with "Escaping unwanted characters, mainly single quotes". So if you want to remove/replace other characters, not just single quotes, experiment in the Immediate window until you find a Replace() expression which works. Then use that expression in your query.

For example, if unwanted characters include line breaks ...

MyString = "foo" & vbCrlf & "bar" : ? MyString
foo
bar
? Replace(MyString, Chr(13) & Chr(10), " ")
foo bar

Note: I used Chr(13) & Chr(10) rather than vbCrlf as the find target because the db engine can use the Chr() function but doesn't know about the named constant (vbCrlf).