[AccessD] FW: CodeL

Thanks, A.D.  I'll forward to the client and see if this solves his problem.

Best,

Rocky
 




 	
	

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
Sent: Saturday, March 08, 2008 12:39 AM
To: Access Developers discussion and problem solving
Cc: A.D.Tejpal
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG. 
Version: 7.5.518 / Virus Database: 269.21.6/1318 - Release Date: 3/7/2008
2:01 PM