[AccessD] SQL INSERT Question

Appending form / subform values to destination table
(Use of SELECT instead of VALUES clause)
===================================

    In general, while building the append SQL, there is a distinct advantage in using SELECT instead of VALUES clause, wherever feasible. Some advantages of doing so are mentioned below:
    (a) No need to worry about specific enclosing characters while concatenating values for various data types (e.g. ' for text type and # for date type data). Moreover, there is no problem if the value held by any of the controls happens to be Null (provided destination table is in a position to accept Null value in the field concerned).
    (b) No need to fix any embedded quotes, whether single or double.
    (c) No risk of dates getting mis-interpreted (in the process of concatenation into SQL string) when local system settings for short date are not as per US settings (mm/dd/yyyy).

    Following three styles of SQL for append action can be considered for values held by controls on an independent form as well as those on a subform:
    (a) Use form/subform values directly. This is preferred, being the most straight forward.
    (b) Use form/subform values via recordset.
    (c) Use form/subform values via array.

    A dummy table named T_Dummy with single field single record is used for facilitating adoption of SELECT clause instead of VALUES clause. Actual contents of this table are of no significance.

    My sample db named Query_AppendValuesViaSelectClause demonstrates the above approach. It is available at Rogers Access Library (other developers library). Link - http://www.rogersaccesslibrary.com/OtherLibraries.asp#Tejpal,A.D. 
 
    It covers main as well as subform, each with controls having different types of data (i.e. text with embedded quotes, date & number). Pertinent controls (on form or subform) along with freshly appended record in destination table get highlighted in special color. Simultaneously, the SQL actually used for the selected append action is displayed in a text box on the main form - for ready reference.

Best wishes,
A.D.Tejpal
------------

  ----- Original Message ----- 
  From: Michael R Mattys 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, January 24, 2008 06:01
  Subject: Re: [AccessD] SQL INSERT Question


  Hi Susan,

  Uh, well ... I was wrong ... just one rs
  Anyway ... enjoy your evening :)

  Michael R. Mattys
  MapPoint & Access Dev
  www.mattysconsulting.com

  ----- Original Message ----- 
  From: "Susan Harkins" <ssharkins at gmail.com>
  To: "Access Developers discussion and problem solving" 
  <accessd at databaseadvisors.com>
  Sent: Wednesday, January 23, 2008 7:21 PM
  Subject: Re: [AccessD] SQL INSERT Question


  > I've never tried a JOIN without VALUES, but theoretically, I guess it 
  > could
  > work if all the fields are covered -- but I've never tried. Honestly, I
  > missed that they were coming from different recordsets.
  >
  > Susan H.
  >
  >
  >> http://www.w3schools.com/sql/sql_insert.asp
  >>
  >> Since the values were coming from 2 recordsets,
  >> the VALUES statement made sense given the
  >> lack of FROM <TABLE> in the SQL string.
  >>
  >> As the saying goes, "There's more than one way to skin a cat"
  >>
  >> Michael R. Mattys
  >> MapPoint & Access Dev
  >> www.mattysconsulting.com