Escaping ' in Access SQL

Question

I'm trying to do a domain lookup in vba with something like this:

DLookup("island", "villages", "village = '" & txtVillage & "'")

This works fine until txtVillage is something like Dillon's Bay, when the apostrophe is taken to be a single quote, and I get a run-time error.

I've written a trivial function that escapes single quotes - it replaces "'" with "''". This seems to be something that comes up fairly often, but I can't find any reference to a built-in function that does the same. Have I missed something?

Answer 1

Parametrized queries such as Joel Coehoorn suggested are the way to go, instead of doing concatenation in query string. First - avoids certain security risks, second - I am reasonably certain it takes escaping into engine's own hands and you don't have to worry about that.

Answer 2

The "Replace" function should do the trick. Based on your code above:

DLookup("island", "villages", "village = '" & Replace(txtVillage, "'", "''") & "'")

Answer 3

For who having trouble with single quotation and Replace function, this line may save your day ^o^

Replace(result, "'", "''", , , vbBinaryCompare)

Answer 4

It's worse than you think. Think about what would happen if someone entered a value like this, and you haven't escaped anything:

'); DROP TABLE [YourTable]

Not pretty.

The reason there's no built in function to simply escape an apostrophe is because the correct way to handle this is to use query parameters. For an Ole/Access style query you'd set this as your query string:

DLookup("island", "village", "village = ? ")

And then set the parameter separately. I don't know how you go about setting the parameter value from vba, though.