Form allows text entry; how to stop apostrophes from breaking SQL?

[Rachel Garrett]

I have a form with a text box (Memo data type) that allows user
comments. I also run some SQL later on that inserts these comments
elsewhere. If the user types apostrophes (like in the words don't,
let's, they're, etc.), then this will break the SQL. I would like to
write some VBA code to put escape characters into the user's comments
*before* it gets sent to SQL. Any suggestions?

 

[John W. Vinson]

I have a form with a text box (Memo data type) that allows user
comments. I also run some SQL later on that inserts these comments
elsewhere. If the user types apostrophes (like in the words don't,
let's, they're, etc.), then this will break the SQL. I would like to
write some VBA code to put escape characters into the user's comments
*before* it gets sent to SQL. Any suggestions?

Well, just don't let it break the SQL. Use " to delimit the inserted string
rather than '.

Or, use

Replace(memotext, "'", "''")

to replace all instances of ' with two consecutive ', which will be parsed
back to a single apostrophe.

Perhaps you could post your current code or the SQL of the query.

 

[Rachel Garrett]

Thank you, the Replace command is what I needed. This works!

 

[gunslingor]

Doesn't that cause it to still display as double single quotes? or is that an escape character? I mean, I want my users to be able to type in the txtbx just as if they were typing in word... no restrictions, no dangers of SQL stupidity. Why doesn't SQL simply treat the data as objects? I mean, I can't image why they'd design it so that entering values in a text box has any potential to run a query...shouldn't they just have another textboxish type object call SQLbox or something. Access 2014 maybe?