pass-through query syntax

[MikeR]

I have a pass-through query having difficulty with names
with apostrophes. If the last name is "O'Toole", I get an
error message "unclosed quotation mark before character
string"

Here is my WHERE clause which is changed programmatically:

sWhereClause = " p.LastName LIKE '" & Me.ID & "%' "

How have others solved this?
I suppose I could use the InStr function to determine if
an apostraphe is present and write a different WHERE
clause just for that situation, but that's a few lines of
code.

 

[[MVP] S. Clark]

You'll need to add a second apostrophe to get it through.

i.e. O''Toole

So, the InStr function will assist with that.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Brian Camire]

The short answer is that you can avoid this problem if you replace each
apostrophe with two consecutive apostrophes, as in something like

sWhereClause = " p.LastName LIKE '" & Replace(Me.ID, "'", "''", 1, -1,
vbBinaryCompare) & "%' "

in Access 2000 or later.

The long answer is that you should avoid using literal values in your SQL
statement if you are at all concerned about security (for more on this, do a
search on "SQL Injection") and performance (your query will need be
prepared/parsed/compiled the server each time it's executed). You should
consider using a parameter query instead. This might be: (1) a "regular"
non-pass-through parameter query on a linked table to the underlying table
on your server, (2) a parameterized ADO Command that you create in a code
against an ADO Connection to your server, or, (3) a parameterized DAO
QueryDef that you create in code against an ODBCDirect connection to your
server.

Hope this helps.

 

[Guest]

I don't have very clear your syntax.

Why using the single quotations and the "%"
Me.ID is holding the Second Name string? is a textbox,
combobox?

 

[MikeR]

Thanks so much!
Mike

-----Original Message-----
You'll need to add a second apostrophe to get it through.

i.e. O''Toole

So, the InStr function will assist with that.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-




.

 

[MikeR]

Thanks! I like the short answer for now but will check out
the long answer.
Mike