DoCMD.RunSQL with data that has a ' in it

[Jordan]

I am running into a problem when I try to do an insert of a text value using
DoCmd.RunSQL when a text parameter has an ' in the data. I have a line in
my routine to insert data into table "tblUsers" with the line below:

DoCmd.RunSQL (sSql)

Where the sSql string is constructed as by grabbing information from an
active directory query here is the line to construct the SQL statement:

sSql = "INSERT INTO tblUsers (FirstName, LastName) _
VALUES ('" & user.FirstName & "', '" & user.LastName & "');"

The problem comes when I get to a value for the users Last Name when their
name has an apostrophe in it like Bob O'Sullivan because the statement ends
up like below:

sSql = "INSERT INTO tblUsers (FirstName, LastName) _
VALUES ('Bob', 'O'Sullivan');"

How can I format the statement to handle text that has an apostrophe in the
name

 

[Allen Browne]

Use the double-quote delimiter instead of the single-quote.
You need to double them up inside a string.

sSql = "INSERT INTO tblUsers (FirstName, LastName) " & _
"VALUES (""" & user.FirstName & """, """ & user.LastName & "");"

Although the same problem does arise if the data contains the double-quote
character, but that is rare in names (generally only in issue as an
abbreviation for inches or seconds.)