INSERT INTO with Values that contain apostrophes

  • Thread starter Thread starter Tom Codamo
  • Start date Start date
T

Tom Codamo

When building a SQL string to be used with an execute
command that is using the INSERT INTO statement and a
list of items in the VALUES part of the statement, commas
are item separators and single quotes enclose the
character/string items. However, some of my
character/string items are employee names that include a
single quote/apostrophe, e.g., D'Arrigo. This apostrophe
is of course interpreted as a character/string item
enclosure and a syntax error results. Is there an
alternate syntax that will accept an apostrophe within a
character/string item in the VALUES list?
 
Use the double-quote character as the text delimiter.

If this is going into a string, double them up so Access understands it is
not the end of the string, e.g.:
"This string contains a ""word"" in doublequotes."

Therefore:
"INSERT INTO MyTable ( Surname ) ""D'Arrigo"" As Surname FROM ...
 
I use a slightly different technique than Allen, with the same effect:

strSQL = "INSERT INTO MyTable ( Surname ) Values (" & chr$(34) & "D'Arrigo"
& chr$(34) & ")"

HTH
Dale
 
Back
Top