Create new recordset based on an existing one?

geralf

Registered User.
Local time
Today, 23:43
Joined
Nov 15, 2002
Messages
212
Hi,

I'm running a procedure where I'm reading all records from a query into a VBA recordset variable. How can I create a new recordset which is filtered by a criteria, based on the VBA recordset variable?

Thanks for any help in advance


Regards
Gerhard
 
I'm running a procedure where I'm reading all records from a query into a VBA recordset variable. How can I create a new recordset which is filtered by a criteria, based on the VBA recordset variable?

i.e. you are basically just setting the Recordset object to the query?

Set rs = db.OpenRecordset("qryMyQuery")

So, why not build your new recordset off the query?

Set rs = db.OpenRecordset("SELECT * FROM qryMyQuery WHERE MyCriteria = 'whatever the criteria is';")
 
Thanks for your fast reply Mile-O-Phile!

The reason is that I want this process to go as fast as possible. Each time the query runs, the data must be fetched from the backend. If I instead run the query once, returning all data and then get the ones I need from the recordset variable instead. Does this make sense or what?

Regards
Gerhard
 
Gerhard,

Something else you may want to experiment with: Run an action query and pour the resultset into a local table. Something like the following...

Code:
Dim sSQL as string

sSQL = " INSERT INTO TableLocal " & _
	"SELECT TableRemote.* FROM TableRemote " & _
	"WHERE SomeField = SomeValue"

CurrentDB.Execute sSQL

Additional queries can be run against the local table...

Regards,
Tim
 
I should think that it's quite simple:
Code:
Dim rs1 as Recordset
Dim rs2 As Recordset

rs1 = Currentdb.Openrecordset...
rs2 = rs1
After that you can filter rs2 with the Filter-property of the recordset.
 
Last edited:
Thank you both for your answer.

Great idea to use a local temporary table, but I'll most certainly want to use that when I need a more permanent place tp put things.

Burt, I looked up the filter property earlier, but where I looked it did not say it applied to recordset objects. I've looked a bit harder and yes, I found an example in Access help files which is what I want.

Thank you all for your replies an d interest in this matter.

Regards
Gerhard
 

Users who are viewing this thread

Back
Top Bottom