Wow. I'm surprised. I figured Execute would be the fastest. No surprise the Recordset is the slowest. It's an iterative loop. How big was your dataset?

Here's what GPT has to say on the subject:

The fastest method for transferring data in Microsoft Access typically depends on the scenario. Here's an overview:

1. `CurrentDb.Execute`  
   - Best for bulk operations (like `INSERT INTO`, `UPDATE`, or `DELETE` queries) that do not require user interaction.
   - Uses DAO (Data Access Objects), which is optimized for Access.
   - Fast and efficient because it bypasses Access UI overhead (e.g., warnings).
   - Example:

Code

     CurrentDb.Execute "INSERT INTO Table2 SELECT * FROM Table1", dbFailOnError
Copy

2. `DoCmd.RunSQL`  
   - Allows running SQL commands but includes Access UI overhead, such as confirmation prompts (unless warnings are disabled with `DoCmd.SetWarnings False`).
   - Slower than `CurrentDb.Execute` because of this additional overhead.
   - Example:

Code

     DoCmd.RunSQL "INSERT INTO Table2 SELECT * FROM Table1"
Copy

   - Recommendation: Use sparingly when UI interaction is required.

3. Recordset Loop  
   - Used when data must be manipulated row-by-row or logic needs to be applied before transferring each record.
   - Slower than the other methods due to the iterative nature.
   - Example:

Code

     Dim rs As DAO.Recordset
     Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table1")
     While Not rs.EOF
         CurrentDb.Execute "INSERT INTO Table2 (Field1) VALUES (" & rs!Field1 & ")", dbFailOnError
         rs.MoveNext
     Wend
     rs.Close
     Set rs = Nothing
Copy

   - Recommendation: Use only when necessary for complex logic.

Recommendation for Speed:
- Use `CurrentDb.Execute` for bulk operations whenever possible, as it is faster and more efficient than `DoCmd.RunSQL` or looping through a recordset.  
- Reserve recordset loops for cases requiring row-by-row processing or conditional logic that SQL alone can't handle.  
- Avoid `DoCmd.RunSQL` unless you specifically need Access to manage warnings or user interactions.

Dammit... now you made me want to test this myself with different sizes and kinds of datasets. LOL.

I thought execute would win too, but isn't it a tie? both 29 seconds?

RunSQL is NOT faster, far from it.  The test above was not done correctly, because each operation was only run once, and the difference was too small to be noticed.  You need to run each operation a great number of times, then compare.  Not only would the result be more noticeable, but it would also reflect real-world applications better, because in any application, you don't just run anything once, but iterate it tens, hundreds, thousands of times in a short amount time due to all the loops in the code (For-Next, Do While, nested loops, etc.).  And all those time intervals would add up and yield dramatic differences over time if optimization was done poorly or not at all.

In my tests pictured below, I ran RunSQL, Execute, and OpenRecordset 10000 times each, each time only inserting a little bit of data into a table.  RunSQL took 50 seconds to run 10000 times.  Execute took only 5 seconds if CurrentDb was not cached, only 2 seconds if cached.  OpenRecordset took 4 seconds if CurrentDb was uncached, 2 seconds if cached.  Invoking CurrentDb is a time-consuming process, which is something we learned in Access 2.0 days.  That's why I always invoke it just once rather than repeatedly, whenever possible.

In short:
1. Never run RunSQL.
2. Cache CurrentDb (save it into a variable).

Thank you Kevin,

Very informative like usual.

Yeah, that's more along the lines of what I would have expected. I'm curious to see if there's a huge difference between a SINGLE call to Execute and RunSQL that handles a huge number of giant records with a single call. Like appending 1,000,000 records and each record has multiple fields and at least one large text field.

I believe that original test in the first post did just that: one transaction that affects a large number of rows.  I mean, it took 30 seconds to run, so the data set must be pretty large.  The key thing is not really about the speed, though.  It is about which occurs more often in an application: 1 or 2 transactions that affect a large number of rows, or a large number of transactions, each affecting only a small number of rows.  In my experience (and likely many others'), it is the latter.  That means we need to optimize these transactions accordingly.

In the initial post, it was mentioned that the RunSQL method proved to be the fastest when copying data to another table. The data source in that instance came from a highly complex query, involving around ten sub-queries and union operations. It seems that when you open this intricate query using `db.openRecordset("ComplicatedQuery")`, the complexity impacts performance, making RunSQL the superior option in terms of speed.

Conclusion: It is crucial to plan ahead and clearly understand your objectives. Depending on what you aim to achieve, choose the most appropriate method accordingly.

Let's remember to be polite, kind, and respectful in our comments, everyone. Thank you. :)