[AccessD] Insert Into 101

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
>>
> 
> 
> 



More information about the AccessD mailing list