[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