[AccessD] Building SQL statement

I thank everyone for their solutions.  In fact this is in VB.Net.  Fir this
particular problem I think the easiest solution will be to open a recordset
and place the string into a specific field.  I do understand the issue
though and the solution if I do want to build a SQL statement.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
Sent: Friday, August 26, 2005 10:40 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Building SQL statement


John,

    Add-In named  SQLToVBAString_ver2.zip, available in folder 0_Utilities
And Add-Ins, in files section of Yahoo group named  MS_Access_Professionals
might be of interest to you.

    Once installed, it coverts an SQL statement into VBA string (duly taking
care of extra quotes if any) and copies the contents to the clipboard for
direct use in VBA editor.

A.D.Tejpal
--------------

  ----- Original Message ----- 
  From: John W. Colby 
  To: 'Access Developers discussion and problem solving' 
  Sent: Thursday, August 25, 2005 08:53
  Subject: [AccessD] Building SQL statement


  I'm attempting to build a SQL statement to append data into a record.  One
  of the fields is text, including (possibly) any valid text including ' -
for
  example in contractions such as "aren't" etc.

  My SQL statement is building up a statement in the format:

              lstrSQL = "INSERT INTO WebNewsItem ( WebNewsSourceID, Link,
  Title, ArticleDescription ) " & _
                          "SELECT " & webNewsSourceID & " AS
WebNewsSourceID,
  '" & link & "' AS Link, '" & title & "' AS Title, '" & description & "' AS
  ArticleDescription;"

  The problem is that description can contain the ' character and thus I end
  up with an invalid SQL statement since the ' embedded in description
"looks
  like" the ' that is supposed to enclose the text.

  I can't for the life of me remember how I am supposed to handle this.

  I could punt and open a recordset, create a new record, and place the data
  directly into the field, which I may end up doing but I sure would prefer
to
  just do the "append query" thing.  It is (was) much simpler.

  John W. Colby
  www.ColbyConsulting.com 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com