Copy Recordset to another Recordset

[JohnPapa]

yes, use the rs.filter property

Dim db As DAO.Database
    Dim rs  As DAO.Recordset
    Dim rs2  As DAO.Recordset


    Dim SQL_Select  As String


    SQL_Select = "Select * from tbl1"


    Set db = CurrentDb()
    Set rs = db.OpenRecordset(SQL_Select)


    rs.Filter = "date1 < #1/1/24# "


    Set rs2 = rs.OpenRecordset


    rs.MoveLast
    rs2.MoveLast
         
    Debug.Print rs.RecordCount, rs2.RecordCount


MyExit:


    rs.Close
    Set rs = Nothing
    rs2.Close
    Set rs2 = Nothing
    Set db = Nothing
   
End Sub

Suppose you could also set the 1st recordset as global.

Actually both recordsets are global. Will try it out. Thanks.

 

[MajP]

The Dao filter is probably worse performance the just a new query

Recordset.Filter property (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

In many cases, it's faster to open a new Recordset object by using an SQL statement that includes a WHERE clause.

 

[MajP]

However there are bigger problems with this dB if that query is slow to populate the RS. I would assume a handful of appointments are returned?

 

[CJ_London]

It says Run-time error 5, Invalid procedure call or argument.

since that code is not what I supplied, would need to see the full code, including declarations

The reason why I do not want to rerun the query is that it can take a lot of time.

on 1/2m records, should not take long - implies fields used in queries for joins and criteria are not indexed

 

[moke123]

What's the purpose of this exercise? Is it to just avoid running the slow query more than once?

 

[JohnPapa]

yes, use the rs.filter property

Dim db As DAO.Database
    Dim rs  As DAO.Recordset
    Dim rs2  As DAO.Recordset


    Dim SQL_Select  As String


    SQL_Select = "Select * from tbl1"


    Set db = CurrentDb()
    Set rs = db.OpenRecordset(SQL_Select)


    rs.Filter = "date1 < #1/1/24# "


    Set rs2 = rs.OpenRecordset


    rs.MoveLast
    rs2.MoveLast
         
    Debug.Print rs.RecordCount, rs2.RecordCount


MyExit:


    rs.Close
    Set rs = Nothing
    rs2.Close
    Set rs2 = Nothing
    Set db = Nothing
   
End Sub

Suppose you could also set the 1st recordset as global.

I made the changes you suggested and it works fine. Thanks.

Once I find some time I will do it the way it was suggested.
1) Get only one recordset, the one with all the appointments
2) Maybe pass it as a parameter to the form or use a public recordset
3) Use a filter on the form to show "All Patient Appointments" or "Future Patient Appointments"

The appointments associated with a patient may range from 1 to 20, in any case not many.

 

[JohnPapa]

What's the purpose of this exercise? Is it to just avoid running the slow query more than once?

Yes the purpose of the exercise is to avoid running the query twice.

 

[cheekybuddha]

Did you see the link I posted in Post #20?

 

[JohnPapa]

Did you see the link I posted in Post #20?

Sorry, just saw it. "Stack" was misspelled and I mistook it for something else.

Thanks, belatedly.

 

[MajP]

I would be interested if the filter recordset provides any efficiency. In my experience it is faster to simply open the new filtered recordset as MS states.

 

[JohnPapa]

I would be interested if the filter recordset provides any efficiency. In my experience it is faster to simply open the new filtered recordset as MS states.

I have tested the Filter option with the large db on my pc and it is very fast. I need to test it on the actual network and will let you know.

As you commented a patient's appointments is a very small number, so at first sight,if I had a choice of filtering say 10 records Vs querying 500000 records, I would choose the former.

 

[CJ_London]

So you want to bring 500,000 records across the network to then filter? Bringing 500,000 records across is what takes the time

 

[MajP]

I had a choice of filtering say 10 records Vs querying 500000 records, I would choose the former.

My understanding is it is not that simple. In that It does not directly filter the recordset but just creates a new filtered connection. Maybe I am incorrect in that interpretation.
@Galaxiom reports good performance.

recordset filter instead of re-opening recordset

I searched all over for a good example of filtering a recordset then refiltering and want the most efficient way of handling the following: I have query returning all people as level name 10 smith 20 jones 20 jackson 40 simpson so my db.OpenRecordSet ("qryAllPeople") will give me...

www.access-programmers.co.uk

 

[cheekybuddha]

In Post #1 @JohnPapa uses a recordset already filtered by patient as the basis.

He seems to just want to toggle between all that patient's appointments and just future appointments without hitting the backend again.

 

[JohnPapa]

In Post #1 @JohnPapa uses a recordset already filtered by patient as the basis.

He seems to just want to toggle between all that patient's appointments and just future appointments without hitting the backend again.

Exactly.