Rocky Smolin at Beach Access Software
rockysmolin at bchacc.com
Fri Mar 7 13:19:30 CST 2008
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 & "" -- 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 Free Edition. Version: 7.5.516 / Virus Database: 269.21.6/1316 - Release Date: 3/6/2008 6:58 PM