Re: Using SQL statement to append a recordset to a table

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 02/19/05 

Date: Fri, 18 Feb 2005 21:38:14 -0500

To insert a value into the field Priority:

sSQL = "INSERT INTO tblOprrig_In1_Not_In2 (Priority) SELECT " & _
    rs1.Fields("Priority").Value

You can use short version for inserting all fields from one table/query into
another, but not from the recordset itself. You'd need to store the
recordset into a table, and then run your append query using that table....
completely unnecessary overhead for what you're doing.

If you want to insert into Priority and Name fields:

sSQL = "INSERT INTO tblOprrig_In1_Not_In2 (Priority, [Name]) SELECT " & _
    rs1.Fields("Priority").Value & ", " & rs1.Fields("Name").Value

Also, do not use Name as the name of a field. It and many other words are
reserved words in ACCESS, and can create serious confusion for ACCESS and
Jet. See these Knowledge Base articles for more information:

    List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

    List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

    Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763 

-- 
        Ken Snell
<MS ACCESS MVP>
"Harold DeMooy" <HaroldDeMooy@discussions.microsoft.com> wrote in message 
news:B74A6158-0B79-4E49-8DE7-18EE169BB570@microsoft.com...
> Argh! - oh well I was hoping for some SQL "Wildcard" notation that would 
> let
> me insert into all the fields in tblOprrig_In2_Not_In2 all the values of 
> the
> current record of rs1.
>
> So as a intermediate step I though if I could get one or two fields
> populated in tblOprrig_In2_Not_In2 with the appropiate values from rs1 
> then I
> could easily (with a lot of tying and proofreading) expand it to all the
> fields/values. I first tried the Field "Priority" as it is the primary 
> key.
> I then spent the afternoon trying many variaitions, such as
>
> sSQL = "INSERT INTO tblOprrig_In1_Not_In2 (Priority) SELECT " &
> rs1.Fields(0).Value
>
> -and the same all in quotes - and
>
> sSQL = "INSERT INTO tblOprrig_In1_Not_In2 (Priority) SELECT
> rs1.Fields("Priority").Value"  <--- this one realy squawked took quote in 
> ("P
> as end of statement
>
> Then tried the Field "Name" as I had already successfully read it into the
> variable sTitle
>
> sSQL = "INSERT INTO tblOprrig_In1_Not_In2 (Name) SELECT sTitle"
> -and-
> sSQL = "INSERT INTO tblOprrig_In1_Not_In2 (Name) SELECT Values(sTitle)"
>
> all failed mean while each time my report was working fine (the record for
> the table used in the report is created just a few lines down in the same
> loop).
>
> I also tried tablename[fieldname] notation and the tablename.fieldname
> notation
>
> Then I manually imported the OPRRIG table from the two Data.mdb's I had 
> been
> using in my testing as OPRRIG_Baseline and OPRRIG_Base265. Used the query
> wizard "Find Unmatched Query Wizard" to create a query between these two
> tables, changed to an append query, appending to the 
> tblOprrig_In1_Not_In2
> table (looking at the sql view along the way). It works!
>
> Maybe I should be looking this route. Modifying the SQL replacing
> OPRRIG_Baseline and OPRRIG_Base265 with the appropriate syntax for rs1 and
> rs2 then use DoCMD to run the query (I know I would have to place it 
> outside
> the current loop to work - maybe set a flag on the first unmatched record
> occurence inside the loop to test against when leaving).
> Any thoughts on this
>
> Thanks again - I've got a long weekend - will be back
> Tuesday
>
> "Ken Snell [MVP]" wrote:
>
>> OK. Look at the SQL line that I provided:
>>
>>           sSQL = "INSERT INTO tblOprrig_In1_Not_In2 SELECT " &
>> rs1.Fields(0).Value
>>
>>
>> You'll note that I am using the value of the first field in the rs1
>> recordset as the value to put in the SQL statement.
>>
>> Second, I see that I neglected to note that you need to specify the field
>> name for the tblOprrig_In1_not_In2 table that is receiving a value. The
>> general syntax for an append query is
>>
>> INSERT INTO TableName
>> VALUES ( Field1, Field2, Field3 )
>> SELECT FieldOne, Field2, Field3
>> FROM AnotherTable;
>>
>> or, when you have just a single record to insert and you have "constant"
>> values
>> for that record (such as you appear to be doing), you don't need
>> AnotherTable:
>>
>> INSERT INTO TableName
>> VALUES ( Field1, Field2, Field3 )
>> SELECT 1, 2, 3;
>>
>>
>> Thus, the SQL step would be something like this:
>>           sSQL = "INSERT INTO tblOprrig_In1_Not_In2 (FieldName) SELECT " 
>> &
>> rs1.Fields(0).Value
>>
>> If you are writing more than one field into the record, then you'll need 
>> to
>> expand the SQL statement accordingly.
>> -- 
>>
>>         Ken Snell
>> <MS ACCESS MVP>
>>
>>
>> "Harold DeMooy" <HaroldDeMooy@discussions.microsoft.com> wrote in message
>> news:9B71F5F8-78D2-4078-A098-2C0B68019E97@microsoft.com...
>> > Thanks Ken for your reply
>> >
>> > However rs1 and rs2 recordsets are complete records not just a field in 
>> > a
>> > record.
>> > The over all structure basically is (rs1 and rs2.Index = "PrimaryKey")
>> >
>> > rs1.MoveFirst
>> > rs2.MoveFirst
>> > Do While Not rs1.EOF
>> >  "other code
>> >  If rs2.NoMatch Then  'is current record rs1 in rs2
>> >   ' if true here is where I am trying to append record rs1 to a local
>> > table
>> > "In1notIn2"
>> >   ' more code for summary or detailed report
>> >  End If
>> >  rs1.MoveNext
>> > Loop
>> >
>> > In regards to dbOpenTable vs dbOpenDynamic - the following may make a
>> > difference.
>> > 1) The current function is working fine (for 5-6 years and four 
>> > versions
>> > of
>> > Access).
>> > 2) All Data.mdb's are single user and have unique names. And only one
>> > Data.mdb is linked at a time.
>> > 3) The comparison form is designed such that user can't go do anything
>> > else
>> > so there is no other activity taking place with any of the linked 
>> > tables.
>> >
>> > Thanks again for your help and I hope this clarifies the problem.
>> >
>> >
>> >
>> > "Ken Snell [MVP]" wrote:
>> >
>> >> First, if the two tables are linked tables, you cannot use the
>> >> dbOpenTable
>> >> argument for the OpenRecordset. Use dbOpenDynamic instead.
>> >>
>> >> Second, I believe you need to change this line
>> >>           sSQL = "INSERT INTO tblOprrig_In1_Not_In2 SELECT FROM
>> >> rs1.Value"
>> >>
>> >> to this line (assuming that rs1 has just a single field, and that the
>> >> field
>> >> is numeric data type):
>> >>           sSQL = "INSERT INTO tblOprrig_In1_Not_In2 SELECT " &
>> >> rs1.Fields(0).Value
>> >>
>> >>
>> >> -- 
>> >>
>> >>         Ken Snell
>> >> <MS ACCESS MVP>
>> >>
>> >> "Harold DeMooy" <HaroldDeMooy@discussions.microsoft.com> wrote in 
>> >> message
>> >> news:833949AE-E20F-472A-AA9C-7C022930930C@microsoft.com...
>> >> > Using Access 2003 on Windows XP  although most of the code was
>> >> > originally
>> >> > Access 97
>> >> >
>> >> > I have an Application.mdb front end that contects to a data.mdb. 
>> >> > There
>> >> > are
>> >> > many of these data.mdb's that are identical in structure but each is 
>> >> > a
>> >> > different scenario for a model. We have had Comparison form that 
>> >> > allows
>> >> > us
>> >> > to
>> >> > to select (not necessarily the one we are currently connected to) 
>> >> > two
>> >> > different data.mdb and ct which tables to compare and the create 
>> >> > either
>> >> > a
>> >> > summary or detailed report. One of the things we check for are 
>> >> > records
>> >> > in
>> >> > db1
>> >> > not in db2 and visa versa. For one of the data tables (120+ fields) 
>> >> > I
>> >> > have
>> >> > been asked to modify the reports so the "Indb1notdb2" and 
>> >> > "Indb2notdb1"
>> >> > records are copied to separate tables.
>> >> > I have created two tables that are in the application.mdb.
>> >> >
>> >> > Here are the relavent sections of code (which resides inside a 
>> >> > function
>> >> > called by the report buttons).
>> >> >
>> >> > '-- here are the assignments near beginning of function
>> >> > Set ws = CreateWorkspace("", "admin", "", dbUseJet)
>> >> >
>> >> > Set db1 = ws.OpenDatabase(Me!cboMDB1, False)
>> >> > Set db2 = ws.OpenDatabase(Me!cboMDB2, False)
>> >> > Set td1 = db1.TableDefs(sTable)
>> >> > 'sTable is a string variable with current table name passed to 
>> >> > function
>> >> > by
>> >> > the form
>> >> > Set td2 = db2.TableDefs(sTable)
>> >> >
>> >> >
>> >> > ' -- and later for recordsets
>> >> > Set rs1 = td1.OpenRecordset(dbOpenTable)
>> >> > Set rs2 = td2.OpenRecordset(dbOpenTable)
>> >> >
>> >> >
>> >> > rs1.MoveFirst
>> >> > rs2.MoveFirst
>> >> >
>> >> > Do While Not rs1.EOF
>> >> > ' many lines of code for other functions of the code
>> >> >   If rs2.NoMatch Then
>> >> >        If sTable = "OPRRIG" Then
>> >> >          sSQL = "INSERT INTO tblOprrig_In1_Not_In2 SELECT FROM
>> >> > rs1.Value"
>> >> >          DoCmd.SetWarnings False
>> >> >          DoCmd.RunSQL sSQL
>> >> >          DoCmd.SetWarnings True
>> >> >
>> >> > I've tried many variations for sSQL (like - rs1.Fields(*).value) 
>> >> > none
>> >> > which
>> >> > have come close to working. I know I have access to the rs1 as I've
>> >> > used
>> >> > it
>> >> > for the report with code like
>> >> >
>> >> >     sTitle = rs1.Fields("Name").Value
>> >> >     sType = rs1.Fields("Type").Value
>> >> >
>> >> > So any help would be appreciated
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > -- 
>> >> > Harold DeMooy
>> >> > Denver Water
>> >>
>> >>
>> >>
>>
>>
>>