Efficeincy is funny that way. I'm going to mention the way that I would do what you are trying to accomplish and then maybe someone will chime in and tell us both if their is a better way.

1st Example: (modification of the first code I sent you)
rst1.open "SELECT tbl1.ID, tbl1.SomeField FROM tbl1;"
with rst1
do wile not .eof
conConnection.execute "INSERT INTO tbl2 (tbl2.ID, tbl2.DataField) VALUES " _
& "(" & rst1("tbl1.ID") & ", '" & rst1("tbl1.SomeField") & "');"
.movenext
loop
end with
rst1.close
set rst1 = nothing

So you can see they we just open the first recordset, move through it one by one and each time issues an INSERT into our second recordset (note the conConnection is an ADODB.Connection object that points at the database containing the tables you want to export to).