[AccessD] FW: CodeL

Updates & Appends involving embedded quotes
(No need to fix any quotes)
=================================

Rocky,

    In Access desktop, there is an interesting technique that drastically simplifies handling of embedded quotes (any number & combination of single and double quotes). This is achieved by avoiding concatenation of a hard value into the SQL string. Instead, necessary evaluation is carried out at run time within the query, during execution (via wrapper function having variant type output).

    Apart from eliminating the need for tedious fixing of embedded quotes, this approach has the added advantage that you no longer have to bother about data types (even Nulls) while concatenating values into SQL strings. Moreover, there is no risk of dates getting mis-interpreted (in the process of concatenation into SQL string) when local system settings for short date are not as per US settings (mm/dd/yyyy).

    For the particular case mentioned by you, sample code as given below, should get the desired results. As stated earlier, this is in context of Access desktop, using DAO. You will have to adapt it suitably for server and ADO.

    Note- Similar approach for append queries is demonstrated in my sample db named Query_AppendValuesViaSelectClause. It is available at Rogers Access Library (other developers library). Link - http://www.rogersaccesslibrary.com/OtherLibraries.asp#Tejpal,A.D.

Best wishes,
A.D.Tejpal
------------

Sample code in general module
'=================================
Sub P_Update()
    CurrentDb.Execute "UPDATE tClients " & _
            "Set [FMemo] = Fn_Memo() " & _
            "WHERE ClientIndex = " & _
            CLID & ";", dbFailOnError
End Sub
'--------------------------------------------------

Function Fn_Memo() As Variant
    Fn_Memo = vMemo

    ' Note - vMemo is a global variable of variant
    '             type, loaded with the memo filed 
End Function
'=================================

  ----- Original Message ----- 
  From: Rocky Smolin at Beach Access Software 
  To: 'Access Developers discussion and problem solving' 
  Sent: Saturday, March 08, 2008 00:14
  Subject: [AccessD] FW: CodeL


  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 & "" 
   
  James W. M. Charlton
  Charlton Weeks LLP
  Attorneys at Law
  (661) 265-0969
  (800) 388-9878