PDA

View Full Version : Can you query a RECORDSET using SQL



JKwan
04-11-2018, 07:33 AM
Wondering if one can do a SQL query on a Recordset, I've searched and Googled, thus far found nothing useful. Currently my report project grabs data from an Access DB using SQL and does some aggregate on the data, works. What I am having trouble is that I like to do another SQL on that same Recordset, I am having trouble doing that. The way I am overcoming this problem is to create a temp table then do a SQL on that temp table. Just wondering if I can skip that step and do a direct query right on the Recordset.

Thanks.

xld
04-11-2018, 11:43 AM
Maybe RS.Filter will do what you want? Take a look at my blog post, https://blogs.msmvps.com/xldynamic/2010/10/10/vba-has-no-class/, it talks of disconnected recordsets, but filter works for a normal recordset.

JKwan
04-11-2018, 12:38 PM
Thank you, xld, I don't think it will do. What I need to do with the Recordset is to run an aggregate SQL on it as well. For that, this is why I don't think the Filter would work. My first query did the initial aggregate, because I am collapsing different body parts (face, ear....to a category all Head), this gives me multiple Head groupings. The second SQL, I run another aggregate to give me one count of the Head injury, hence I would like to query the Recordset. So, I dumped the Recordset into a temp table then do a query to overcome my problem. Hoping for a more direct way to solve my problem....

xld
04-11-2018, 03:48 PM
Could you not filter it by your criteria and then a simple aggregation?

JKwan
04-11-2018, 04:20 PM
i suppose you could, but I will need to filter multiple times. If this is the case, I did rather query my temp table, since this is just a simple group by query. Of course, unless i am totally out to lunch....