DAO Recordsets vs. Action Queries

[Paul H]

My boss prefers opening recordsets and looping through them vs. running a simple action query. I don't have a lot of experience with DAO, but I'm trying to do things his way when possible. Here's an example.



I have an append query that aggregates about 400,000 records and appends them to a table. This takes considerable time, 30 minutes, sometimes longer. As an alternative, I have coverted the SQL to a select query and built it into a string variable which is then used to open a recordset and then loop through it one record at a time adding to the target table. It goes something like this.

SQL1 = "Select Field1, Field2, Field3 FROM Table1 WHERE Field1 is >= 5000 "
Set gRS1 = CurrentDb.OpenRecordset(SQL1)

SQL2 = "Select * From Table2"
Set gRS2 = CurrentDb.OpenRecordset(SQL2) 


With gRS1
.MoveFirst
Do While Not .EOF
gRS2.AddNew
gRS2!Field1 = !Field1
gRS2!Field2 = !Field2
gRS2!Field3 = !Field3
gRS2.Update
.MoveNext
Loop
End With

I think I've written this correctly. Once the original recordset is open, the loop portion runs through all the records in a few minutes, but logically is seems to take as long to open the recordset as it does to run the append query. This is more or less what I expected. Other than for the learning experience, is there any advantage to processing this with DAO or is there a way to speed it up?

[orange]

Are you saying the append query takes much longer than your Loop with the DAO Recordset?

What exactly does the boss have against the stored action query? There are many who will say the stored action query is the preferred way to do things with Access, if the conditions allow it. I'm sure I recently read a post by Pat Hartman on this. I think the gist of the post was the use of memory and some speed issues with vba created queries and sql. The stored action query eliminated some memory and interim steps and performed better.

I did a quick search- here are a few links to thoughts:
http://www.access-programmers.co.uk/...d.php?t=225340 see post #8

[Paul H]

Are you saying the append query takes much longer than your Loop with the DAO Recordset?

What exactly does the boss have against the stored action query?

It is just his preferred method, not mine. I think the speed difference is marginal. Whatever imagined gains there might be are lost in the difficulty in building, debugging and editng SQL string. Another database of his has pages and pages of meticulously constructed SQL. that were hell to read and edit. I have found that a simple table UPDATE works much faster looping through code, but building and opening a large recordset with a complex SQL code has little if any advantage and brings with it a whole new set of headaches. In the future I'll pick the best method to fit the task.