[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 & ""