jwcolby
jwcolby at colbyconsulting.com
Mon Oct 12 13:53:09 CDT 2009
Ohhhh... ;) I understand child like, I spend most of my time there anymore. John W. Colby www.ColbyConsulting.com Reuben Cummings wrote: > 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 >> > > >