[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