[AccessD] Insert Into 101

Yes and no.  No, you can't directly put a recordset into a SQL
Statement.  A recordset is an object in code, a SQL Statement is a
string of text, so it would be like writing a book, and trying to
replace the word truck with a real live truck....

However, with you example below, it should be as simple as:

"INSERT INTO tblMyTable " & gstrSQL

The SQL of the recordset, which is what is going to define the recordset
in the first place, can be put at the end of the INSERT INTO statement
to append those records into the recipient table....

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Collins,
Darryl
Sent: Sunday, October 11, 2009 9: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
The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.