using ADO to return records into Access from SQL server

Currently, in MS Access, using DAO/ODBC I "download" data returned from an SQL Server stored procedure into my MS Access front end application. The resulting data drives a user report.

1) I create a pass thru query where the sql is "EXEC spMyprocedure..." etc
2) I then create a make table query which selects from the pass thru query
3) I run the make table query which will then create a table in MS Access containing the resultset of the stored procedure.

The sample DAO code is:

Dim db as database, sConnect as string
Dim qdfSp as querydef, sqlSp as string     ‘query which executes the sp
Dim qdfMake as querydef, sqlMake as string     ‘query which selects from sp and makes a temp table

Set db = currentdb
sConnect = "....."     ‘whatever ODBC connection string

‘now create and define query which calls the sp
sqlSp= "EXEC spMyProcedure " & anyRequiredParameters     ‘define string which executes sp
‘either delete and recreate queries or redefine it
Set qdfSp = db.Createquerydef("spQuery")
with qdfSp
     .connect = sConnect      ‘it’s a pass thru query
     .sql = sqlSp
     .ODBCTimeout = 0     ‘no time limit
     .ReturnRecords = True
end with

‘now create and define query which makes a table from selecting the sp
sqlMake= "Select .. (fields etc from the resulting output of sp) ... INTO tmpTable FROM " & qdfSp.Name     'select from the pass thru query

Set qdfMake = db.Createquerydef("makeTableQuery")
with qdfMake
     .sql = sqlMake
     .ODBCTimeout = 0     ‘no time limit
     .ReturnRecords = True
     .Execute     ‘execute the make table query
end with

set db=nothing
----------------------------------------------------------

Now as MS have been saying the ADO is the future and that DAO is becoming obsolete, I imagine the above must be possible using ADO. However, after experimenting with ADO, I can't get it to create or insert into an Access table that is easily achieved using DAO. The only way possible I can think of is to create an ADO recordset, and then have to loop thru each record and insert individually into an Access table but that would be inefficient when there are thousands of records involved.
Can someone show  me the ADO code to do this efficiently without inserting individual records or do I simply stick with DAO?