jwcolby
jwcolby at colbyconsulting.com
Mon Oct 12 11:11:23 CDT 2009
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 >> > > >