Error message with value having " ' " in SQL Pass Through query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am getting an error message when trying to create a SQL pass-through query
and a value has a " ' " in it. For example:

Select * from Northwind where Company="John's Tire Shop". I get an error
message of SQL thinking the "John's Tire Shop" is a column. I know that in
ODBC connections there is a check box that says to "USE ANSI Quoted
Identifers". I don't to uncheck that because it is standard for all of our
ODBC drivers not to have that unchecked. Is there a way to resolve this
problem in VBA code or a property in the SQL pass through query?

Thanks in advance,
John
 
If "USE ANSI Quoted Identifiers" is on, then "John's Tire Shop" is see the
same as [John's Tire Shop"]

For SQL-Server, you should always enclose your strings with single quotes
instead of double quotes - writing two single quotes for any enclosed single
quote - and forget about this setting:

Select * from Northwind where Company = 'John''s Tire Shop'
 
Select * from Northwind where Company="John's Tire Shop"

For a __Pass-through__ query you would need to use single quotes
(apostrophes).

SELECT * FROM Northwind WHERE Company = 'John''s Time Shop'

I think you have to double up the single apostrophe in John's.
 
Back
Top