Make persistent a DAO recordset in a class module

Question

I am filtering a DAO recordset for sub-results as part of a set of recursive tasks. I'm trying to speed the routine, and I can see the recordset is being opened fresh every time the class object is instantiated. This step happens many hundreds of times. Isn't there a way to re-use it? The keyword here is persistence, isn't it?

I've tried setting the recordset in the Instantiate event, alternatively from within the functions. I've tried using static (instead of dim or private) to declare the recordset. I've also fiddled also with how the class object is declared and set.

I know a common solution is to change to a specific SQL source for each call, but the query that produces the recordset is itself slow so I don't see that as helpful. And yes, the base tables are optimally indexed.

I'm happy to post code, but is this enough for you to offer any tips?

Answer 1

Is the recordset itself only needing to be created once and then filtered many times?

If so, can you pass the recordset as a parameter into the classes method/function that does the filtering on it? That way the recordset can be created once.