[AccessD] Insert Into 101

Maybe that was where I was confused.

Anyway, just created a temp table 
Create Table _TestA (FirstName text, Surname Text, PersonID long)


And a temp query _TestQ

SELECT mcmPeople.FirstName, mcmPeople.Surname, mcmPeople.PersonID
FROM mcmPeople
WHERE (((mcmPeople.PersonID)<1000));



And appended records from the query into the table

Sql = "insert into _TestA select * from _TestQ"

All of that seemed to work ok, have I got it right now?

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: 12 October 2009 16:40
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Insert Into 101

Hi Max

Well, yes and no. Your "myselection" is a (temp) table, not a recordset. So
no cigar. Or?

/gustav


>>> max.wanadoo at gmail.com 12-10-2009 17:22 >>>
No, not one record.  As many as the select query pulls out.

Ie:
Sql = "Insert into mytable select * from myselection"

Live example:

sql = "INSERT INTO mcmGiving SELECT * FROM mcmGivingmports;"

This puts ALL the records from the mcmGivingImports (selected with the *)
into mcmGiving.  I use it daily.

I could equally have put a WHERE clause in there to limit the records
returned.

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: 12 October 2009 16:09
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] Insert Into 101

Hi Max

Your example was for one record. Problem is passing a full recordset to SQL
without some looping where you (_very_ slowly) run one SQL command for each
loop. Not doable in any other way as far as I know.

And as JC states: Why? A DAO loop is by far superior for this kind of
transactions.

/gustav


>>> max.wanadoo at gmail.com 12-10-2009 16:59 >>>
I must be missing something here.  I have posted two examples  of what I
*think* is being asked.  I use this tons of times.  I think that maybe I
have got the wrong end of the  question?

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Reuben Cummings
Sent: 12 October 2009 15:55
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Insert Into 101

I have to disagree, John, as to asking why.  I would love to find a way to
use a recordset in this exact manner.  I've run across several situations
where this very thing would eliminate a lot of headaches.  And, like Darryl,
you either end up looping thru recordsets or making temp tables and using
append queries.


Reuben Cummings
GFC, LLC
812.523.1017


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Monday, October 12, 2009 9:40 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Insert Into 101
> 
> 
> I don't think so.  The obvious question you will be hammered 
> with is "why"?.
> 
> John W. Colby
> www.ColbyConsulting.com 
> 
> 
> Collins, Darryl wrote:
> > Hi Folks,
> > 
> > I know I can do this
> > 
> > INSERT INTO tblMyTable
> > SELECT * FROM tblMyOtherTable
> > 
> > Is there any way of using SQL to select a recordset and then using that
> > RS in the insert statement?
> > 
> >     cnn.Open DbADOConStr
> >     Set rst = New ADODB.Recordset
> >     rst.CursorLocation = adUseClient
> >     rst.Open gstrSQL, cnn, adOpenForwardOnly, adLockReadOnly
> > 
> >     INSERT INTO tblMyTable
> >     SELECT * FROM  " & rst
> > 
> > I have a work-around for this already, but it involves looping and
> > cloning recordsets and jumping thru other hoops.  Frankly it seems very
> > inelegant and inefficent (even though it is fast enough for my
> > purposes).  I am sure there must be a way of getting a recordset for the
> > backend and just dumping the whole thing into a local table (assuming
> > all the fields line up ofcourse).
> > 
> > Cheers
> > Darryl



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