Reuben Cummings
accessd at gfconsultants.com
Mon Oct 12 12:43:10 CDT 2009
That's what I'm getting at in my child-like example, John. It just never occurred to me in the past. 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 12:11 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Insert Into 101 > > > Yes but you use the SQL statement to pull the recordset into > the recordset object. Use that same > SQL statement as the select for a direct insert statement. > What am I missing? > > > John W. Colby > www.ColbyConsulting.com > > > Reuben Cummings wrote: > > Why? > > > > Because we want to avoid looping... > > I have a recordset of X number of records. Would it be > nice to just INSERT > > once rather than looping X number of times. > > > > I'm not sure, from my perspective, it's even about > processing time. It's > > probably more a matter of principle for me. I just think > it makes more > > sense - and I don't cope well with things that I can > obviously see a better > > way, but can't find that way. > > > > Max, I hadn't seen your solution when I was responding... > > > > If you run a recordset that returns records could you then > just turn around > > append that same sql to a "Insert" statement? > > > > Like... > > > > Strsql = "fake query" > > Rst.open strsql > > If not rst.eof then > > 'there are records so they need appended to table X > > docmd.runsql "insert into X (fA, fB, fC) " & strsql > > End if > > > > That is obviously a very rough example. I don't know if > I've tried this in > > the past or not. > > > > Reuben Cummings > > GFC, LLC > > 812.523.1017 > > > > > >> -----Original Message----- > >> From: accessd-bounces at databaseadvisors.com > >> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of > >> Gustav Brock > >> Sent: Monday, October 12, 2009 11:09 AM > >> 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 > >> > > > > > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >