[AccessD] FW: CodeL

Hi Rocky

Yes, it will replace any single-quote with two single-quotes.

They are "allowed" but when building a string like your friend does, a single single-quote or double-quote will jam the code:

"Set [Memo] = 'O'Hare' " & _

/gustav


>>> rockysmolin at bchacc.com 07-03-2008 20:19 >>>
Gustav:

Within the data that he's trying to insert into the SQL table there is a
pair of apostrophes.  Will your code have the effect of replacing them?  I'm
not sure he wants to replace the apostrophes with quote marks.  Are
apostrophes not allowed in a SQL character field?

Thanks and regards,

Rocky
	

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, March 07, 2008 10:55 AM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] FW: CodeL

Hi Rocky

It is probably double single-quotes and not single double-quotes that are
needed.
Try with:

        "Set [Memo] = '" & Replace(vMemo, "'", "''") & "' " & _

/gustav


>>> rockysmolin at bchacc.com 07-03-2008 19:44 >>>
Dear List:
 
Client (see below) is connecting to a SQL back end with ADO (he's doing his
own programming and calling me occasionally for help).  
 
When trying to update a memo field with embedded apostrophes he get a syntax
error.  He says that when he connected with ODBC he didn't have the problem.
 
The code below errors on the Conn.Execute with "incorrect syntax". 
 
I told him to change the apostrophe delimiters around vMemo to double quote
marks.  Then he gets an error which says 'can't be longer than 128
characters.  Memo field is defined as nvarChar 4000.  

I'm pretty weak in SQL and ADO.  Is there a solution to this?
 
MTIA
 
Rocky
 
'ADO connection to MS Sql:
 
vMemo is a declared Variant that is loaded with the memo filed and then when
dirty, I am attempting to update the SQL back end.  CLID is the integer ID
number of the current client
 
    Conn.Execute "UPDATE tClients " & _
        "Set [Memo] = '" & vMemo & "' " & _
              "WHERE ClientIndex=" & CLID & ""