[AccessD] FW: CodeL

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.