[AccessD] SQL INSERT Question

Hi A.D.,

I'm sure everyone will benefit from your diligent watch
for explaining difficult or rarely used concepts.

Thanks for that excellent research and sharing results
for all visitors to Roger's Access Library.

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

----- Original Message ----- 
From: "A.D.Tejpal" <adtp at airtelbroadband.in>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Cc: "A.D.Tejpal" <adtejpal at gmail.com>
Sent: Thursday, January 31, 2008 2:34 AM
Subject: Re: [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
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com