Utilizing a recordset in a query via VBA

[ideafixer]

If I have a recordset set up, can I then run an SQL statement on it or define a subset of that recordset?

Basically, I have two tables. First I perform a union query on them. From the result of my union query, I then want to perform a group by. Basically, a group by on the resulting recordset.

I know I can make a query def of the union, then add the query def to my next query and perform the group by. However, I would like to avoid that all together and somehow accomplish this task through recordsets.

Any suggestions?

 

[cmmrfrds]

"If I have a recordset set up, can I then run an SQL statement on it or define a subset of that recordset?"

If this is an ADO recordset you can create subsets with the filter function. There are quite a number of options for the filter.

rs.filter = "yourfield = 'sometext'"
rs.sort = "yourfield DESC"
'- process subset
rs.filter = adFilterNone '- set the filter off

 

[ideafixer]

I am working with a DAO recordset. It is part of my Access database. Usually I do not set up an ADO recordset unless I am connecting to an outside source. With a filter, I am not sure how to set up a group by. Essentially I want to group by records on one key field then take the max value of 4 other fields.

First Query "Duo Data":
Select IDNumber,SM_Tax_Base_Date,BBG_Tax_Base_Date, '' as SM_Expiration_Date,'' as BBG_Expiration_Date from Tbl_TaxBaseDateDiscrepancies

UNION Select IDNumber,'' as SM_Tax_Base_Date,'' as BBG_Tax_Base_Date,SM_Expiration_Date, BBG_Expiration_Date from Tbl_ExpirationDateDiscrepancies;


Second Query:
SELECT [Duo Data].IDNumber, Max([Duo Data].SM_Tax_Base_Date) AS MaxOfSM_Tax_Base_Date, Max([Duo Data].BBG_Tax_Base_Date) AS MaxOfBBG_Tax_Base_Date, Max([Duo Data].SM_Expiration_Date) AS MaxOfSM_Expiration_Date, Max([Duo Data].BBG_Expiration_Date) AS MaxOfBBG_Expiration_Date
FROM [Duo Data]
GROUP BY [Duo Data].IDNumber;

Once I get the Union grouped, I am generating a report from the second query.

 

[smandoli9]

My understanding is that SQL recordsets are static and can't be manipulated much in VBA. You can do the sorts of things we expect from nested queries in Access, but not by running SQL processes on SQL recordsets.

The only solution I have had to this problem is to loop through the union query recordset, incrementally counting into variables as I go. An array would be useful for collecting the results into a recordset. Alternative to an array would be a table bucket, and while I hear your hiss of distain, that might be useful for development -- then switch to an array.

 

[cmmrfrds]

A recordset cannot be the data source for a Report, although it can be on a Form. Does the query you have setup not work for you??

 

[ideafixer]

Correct. I was planning on defining a recordset in the on open event of my report. That recordset would be my union query. Then from that recordset I would set the me.recordsource = a query based on the defined recordset. Kind of a shot in the dark.

The query works fine. I will store the queries and run the report off of them. I still will accomplish the same thing. Just like trying new things.

 

[cmmrfrds]

I have done something similiar on a Form since it has a recordset object, but the Report does not.

 

[ideafixer]

I have never bound a form to a recordset. Usually I have a form unbound or bound to a table.

A report can have an open or load event of me.recordsource = "Select * from sometable" So can a form.

Are you saying a report cannot have me.recordsource = "any record set" but a form can have me.recordsource = "any record set"?

 

[cmmrfrds]

Maybe there is confusion between recordset and record source

"Are you saying a report cannot have me.recordsource = "any record set" but a form can have me.recordsource = "any record set"?"

Me.RecordSource = "select ...." will work on both.

Dim rs as New ADODB.Recordset
retrieve records etx...
Set Me.Recordset = rs will work on a Form.

A recordset is the result of a query. A recordsource is where the data is coming from like "select ...", a table, or other source on a Form or Report or listbox etc...

 

[ideafixer]

I think my explaination was just a little vauge. I understand record source vs. recordset however was not aware a report's record source was unable to refer to a recordset.

I also was wondering if a recordset could then be used in a query through VBA. It appears you can set critera on a recordset using a filter but outside filtering, a recordset cannot be maniplulated. Thanks for the input.