[AccessD] Insert Into 101

Darryl,

<<Is there any way of using SQL to select a recordset and then using that
RS in the insert statement?>>

 No, not with DAO or ADO I'm afraid.  

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Collins, Darryl
Sent: Sunday, October 11, 2009 10:55 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Insert Into 101

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

"This e-mail and any attachments to it (the "Communication") is, unless
otherwise stated, confidential,  may contain copyright material and is for
the use only of the intended recipient. If you receive the Communication in
error, please notify the sender immediately by return e-mail, delete the
Communication and the return e-mail, and do not read, copy, retransmit or
otherwise deal with it. Any views expressed in the Communication are those
of the individual sender only, unless expressly stated to be those of
Australia and New Zealand Banking Group Limited ABN 11 005 357 522, or any
of its related entities including ANZ National Bank Limited (together
"ANZ"). ANZ does not accept liability in connection with the integrity of or
errors in the Communication, computer virus, data corruption, interference
or delay arising from or in respect of the Communication."

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com