Copy and paste data from one table to another without using append 1

[iamthebestuk]

Hi,

I've searched the threads and have not found an answer to this.

I'm trying to copy the contents of one table to paste onto another - without using the append funtionality - too slow and doesn't work in this siuation.

So far I've got...

Dim ds As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("ALL")
Set rs2 = db.OpenRecordset("All Test")

Where "All" is the table with data and "All Test" is empty.

I thought I may be able to use the DoMenuItem funtionality - but no luck.

Is someone able to help?

Thanks.





Power is Knowledge
Knowledge is Power

 

[PHV]

Have a look at append query, here a starting point:
DoCmd.RunSQL "INSERT INTO [All Test] SELECT * FROM [All]"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[iamthebestuk]

Just tried it - works much better than when I manually created a query using design view. Only took a few seconds to append 53k records with lots of indexes.

I recommend anyone who has tried to use an append query in design view to try this instead.

Excellent.



Power is Knowledge
Knowledge is Power

 

[MajP]

Are you saying that the same append query run from code is much faster than the identical query run from the Query Development Environment? If so, why is that? Can this be generalized?

 

[iamthebestuk]

Exactly - this is what happened to me. The code query was much faster. Not sure this can be universally applied - but I suggest you experiment.



Power is Knowledge
Knowledge is Power

 

[MajP]

Anyone know why this is true? Is the a general problem with the QDE?

 

[NorthNone]

I thought queries did some optimization before run-time (behind the scenes) such that they tended to be faster than code. So I'm puzzled myself.

---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[PHV]

I thought that iamthebestuk did a Copy then a Paste append, which as nothing to do with SQL code (optimized or not).

 

[NorthNone]

You are correct, PHV - my mistake in not reading the original post carefully...

---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.