Reuben Cummings
accessd at gfconsultants.com
Mon Oct 12 10:30:26 CDT 2009
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 >