Set rst = SQL then paste recordset into existing table

[jojones]

I currently have a Make-table Query (qryMake) which outputs the result to a table in a seperate database. It takes anywhere from 2 minutes to 10 minutes to run and I would like to make this quicker by using recordsets if possible.

I have also set qryMake as a querydef and executed it which does run quicker, but ... well, hear me out.

If I us the same SQL conditions as qryMake, but in a select query (qrySel) and open a recordset from qrySel it only takes a few seconds.

So - I have my recordset with the data in it that I want selected. How do I get this into the table in the other database?

Can I somehow paste the data from a recordset into a table? or transfer it? or something? I can't seem to find a thread which explains it. I found one that seems to say this is not possible, but I hope I misread it.

Thanks in advance

 

[dds82]

What you can do is loop through the recordset and execute an "INSERT" SQL statement for each record in the recordset.

 

[jojones]

dds82 - I am not sure what you mean.

I have just tried this: (after setting rsts)

myRst.MoveFirst
Do
With myRst2
.AddNew
For myInt = 0 To 37
.Fields(myInt) = myRst.Fields(myInt)
Next myInt
.Update
End With
myRst.MoveNext
Loop Until myRst.EOF

which I think is similar to what you mean, but this method is slower than using the querydef. I have about 120,000 records.

Any more ideas?

 

[dds82]

Have you tried a SELECT...INTO query?

Try:

SELECT *
FROM my_select_query_that_runs_fast
INTO my_new_table;

 

[dds82]

Of course, the table must exist before you try that. ;-)

 

[KyleS]

jojones,
give dds82's idea a try, but I think overall we're missig the grander point here. It is neigh-impossible to write code that will outrun JET engine SQL. The select query works nice and quickly, then you know SQL works fine and everything is indexed properly. Can you create your tables once, then use DELETE and APPEND (INSERT...) queries. These will run faster since when using a MAKE-TABLE Access needs to delete the table from the dB and then create a new one, where-as with the DELETE query, it's just deleting the data within the table. And appending takes no schema-changes, just some data being thrown into a query. Kyle

 

[jojones]

I have gone with the DELETE * and then Append.

thanks for all your help.

 

[KyleS]

jojones,

We're all more than happy to help!

by the way, is there a noticeable difference in performance? Kyle