Arthur Fuller
fuller.artful at gmail.com
Fri Mar 7 16:07:29 CST 2008
This is overkill, I think, Eric. You just have to know how to handle SQL containing either single or double quotes or neither. It's quite straightforward, really. You can eliminate all the hassle for any column that might contain these values by doubling up on the single and double quotes, which you can do in exactly one statement each: Replace( [columnName], Chr(39), (Chr(39) & Chr(39) ) and similarly Replace( [columnName], Chr(34), (Chr(34) & Chr(34) ) You can wrap both calls into a function if you wish. The code does nothing on values that don't contain the offending characters. Having been bitten by surnames like O'Hara and embedded quotations, if I suspect that this might ever occur, I just pre-empt it right from the beginning. hth, Arthur On 3/7/08, Eric Starkenburg <eric.starkenburg at home.nl> wrote: > > Hi guys, > > Don't use SQL with text that can contain either ' or " to save in a > tabledef > but just use a recordset instead. Using SQL will leave you in trouble.... > > Regards, > Eric > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin at > Beach Access Software > > Sent: Friday, March 07, 2008 9:53 PM > To: 'Access Developers discussion and problem solving' > Subject: Re: [AccessD] FW: CodeL > > I forward and find out. Never saw that before but that don't mean > nothin'. > Lots of stuff in Access *I* never saw before. > > Rocky > > > > > > > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte > Sent: Friday, March 07, 2008 12:19 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] FW: CodeL > > > Can you use the pipe(|) as a delimeter? > > > > From: rockysmolin at bchacc.com > > To: accessd at databaseadvisors.com > > Date: Fri, 7 Mar 2008 12:12:22 -0800 > > Subject: Re: [AccessD] FW: CodeL > > > > Mark: > > > > I'm using apostrophe and single quote interchangeably. > > > > The data contains an occurrence of a double apostrophe. > > > > Whether he delimits it in the SQL statement with a double quote (by > > using a double double quote) or an apostrophe, he gets an error. > > > > Regards, > > > > Rocky > > > > > > > > > > > > > > > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A > > Matte > > Sent: Friday, March 07, 2008 11:40 AM > > To: Access Developers discussion and problem solving > > Subject: Re: [AccessD] FW: CodeL > > > > > > > > Is there a difference between and apostrophe and a single quote? > > > > I think the delimeter is the problem...you are using a > > delimeter...that is also text in your data? > > > > Mark A. Matte > > > > > >> From: rockysmolin at bchacc.com > >> To: accessd at databaseadvisors.com > >> Date: Fri, 7 Mar 2008 11:19:30 -0800 > >> Subject: Re: [AccessD] FW: CodeL > >> > >> 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 > >> > >> > >> -- > >> AccessD mailing list > >> AccessD at databaseadvisors.com > >> http://databaseadvisors.com/mailman/listinfo/accessd > >> Website: http://www.databaseadvisors.com > > > > _________________________________________________________________ > > Shed those extra pounds with MSN and The Biggest Loser! > > http://biggestloser.msn.com/ > > -- > > 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 > > > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > _________________________________________________________________ > Shed those extra pounds with MSN and The Biggest Loser! > http://biggestloser.msn.com/ > -- > 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 > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > > > > > _____ > > avast! Antivirus <http://www.avast.com> : Uitgaande bericht is niet > besmet. > > > Virus Gegevensbestand (VPS): 080307-0, 07-03-2008 > Getest op: 7-3-2008 22:36:17 > avast! - auteursrecht (c) 1988-2008 ALWIL Software. > > > > > -- > > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >