MS Access: Quoting Strings in SQL

I was having a real WTF moment with Access. I’d coded up an SQL query in access, and a string had a single quote in it, fouling up the query.

The SQL was something like this:

SELECT * FROM Places WHERE Name='Joe's Bar'

Obviously, I forgot to quote the string correctly. For some reason, web searches didn’t really turn up much about quoting text strings in SQL statements in Access. There was a lot of code that looked like this:

sql = "SELECT * FROM Places WHERE NAME='" & name & "'"

My code was like that too, because that’s what everyone was doing. What’s funny is that I’ve used paramterized queries in Java, and written some similar tools for PHP, but back in VBA, I use that broken style.

Knowing the right way to do it, I googled for notes about using parameterized queries in MS Access and Jet. It looked hard. It also looked verbose, and it was a little confusing.

Further searches turned up results about quoting strings, but they were kind of “not pretty”:

sql = "SELECT * FROM Places WHERE NAME='" & Replace(name,"'","''") & "'"

Well, at least it’s explicit.

Instead, here’s a half-way solution that cleans up the code a bit. It’s inspired by Perl::DBI’s quote function, which will escape quotes and also add quotes around the string:

' Single quote a string (and escape contents)
Public Function SQuote(s As String) As String
    SQuote = "'" & Replace(s, "'", "''") & "'"
End Function

' Adds a comma, so you can create constructions like:
' SQuoteComma(foo) & SQuoteComma(bar)
' Result: 'foo''svalue','bar''svalue'
Public Function SQuoteComma(s As String) As String
    SQuoteComma = SQuote(s) & ","
End Function

Public Function DQuote(s As String) As String
    DQuote = """" & Replace(s, """", """""") & """"
End Function

Public Function DQuoteComma(s As String) As String
    DQuoteComma = DQuote(s) & ","
End Function

Now the statement looks like this:

sql = "SELECT * FROM Places WHERE NAME=" & SQuote(name)

Also, if you have an INSERT statement, you can construct a comma-separated list of strings like this:

sql = "INSERT INTO Places (Name,Street,City) VALUES (" & _
    SQuoteComma(name) & SQuoteComma(street) & SQuote(city) & _
    ")"

Even with the long function name, it’s fewer characters than “‘” & “‘”.