Can you query an ADODB.Recordset with another ADO Query?

[bs0d]

I'm thinking about stacked queries in Access. And instead of saving fixed queries in the database, might there be a way using VBA with ADO to:

• Establish a "base" query.
• Reference that query in a SQL statement for another ADO query?

For example:

Base query selects records from multiple tables with x and y conditions AS [My_Base_Query]

Then, in the SQL statement of another ADO request,

Select field1, field2, field3 FROM [My_Base_Query]

If so, can someone give me an example? Thanks

 

[Kyle123]

No, i believe you are looking for a view. I think access calls them queries (but not 100% sure)

 

[bs0d]

No, i believe you are looking for a view. I think access calls them queries (but not 100% sure)

Thank. Might there be a way to take a view/query in Access and modify an existing WHERE statement before querying it, or append a WHERE to it before querying?

 

[Norie]

Couldn't you use a subquery?

 

[bs0d]

Couldn't you use a subquery?

I'm toying around with sub queries and they seem to run very very slow. I believe this is due to the stored query which is references does not have effectively a condition applied to it to limit the results. This is because the condition with which to limit can change depending on the request by the user.

So either I manually create the same views over and over in the Access DB, predicting the possible fields which the query can be filtered by, then select the appropriate query name in VBA before querying it --- or -- I find a way to limit/parameterize them dynamically before querying. This lead me to my initial question.

I'm definitely not a query guy and I've been trying to read up. What about stored procedures?

 

[Norie]

When you query a stored query the stored query still has to be executed.

What is it you want to do with the 2nd query?

Is the only thing that changes the fields you want to return?

 

[bs0d]

In my first query, I pull together all daily readings for all entities. I must do this because the data is (not by my design) is different tables and in different databases. The first query brings it all in one place.

In my objective query, I either query the daily readings for a single entity; or query the sum of the daily readings by date for a group of entities, such as: Where tblProperties.GROUP = "my group" and tblProperties.AREA = "my area". A group request may also be requested as WHERE tblProperties.Year = 2014 and tblProperties.AREA = "my area", you see what I mean?

So it would be nice to tee up the first query (with either a year, area, group or two of three conditions), and then proceed to total/average/sum/normalize.

 

[bs0d]

Here is an example query that runs slow if I reference the named query [_Group_Products], but executes fast if I reference a local table of the same data:

SELECT 
Q1.TimeDay, 
Sum(Q1.SumOfDailyProd1), 
Sum(Q1.SumOfDailyProd2), 
Sum(Q1.SumOfDailyProd3), 
Count(Q1.Item_KEY) AS CountOfItem_KEY

FROM 

   (SELECT 
   [_Group_Products].SiteID AS Item_KEY, 
   [_Group_Products].[Date] AS ReadingDate, 
   Sum([_Group_Products].DailyProd1) AS sumofDailyProd1, 
   Sum([_Group_Products].DailyProd2) AS SumOfDailyProd2, 
   Sum([_Group_Products].DailyProd3) AS SumOfDailyProd3, 
        (SELECT 
        COUNT(Table1A.[Date]) 
        FROM [_Group_Products] AS Table1A  
        WHERE Table1A.[Date]<=[_Group_Products].[Date] AND Table1A.SiteID=[_Group_Products].SiteID) AS TimeDay
   
   FROM [_Group_Products] INNER JOIN tblProperties ON [_Group_Products].SiteID = tblProperties.WH_IDX
   WHERE (((tblProperties.GROUP)="TEST") AND ((tblProperties.AREA)="TEST"))
   GROUP BY [_Group_Products].SiteID, [_Group_Products].Date)

AS Q1

GROUP BY Q1.TimeDay;

I think what is slowing it down is the count(table1A) sub query, which doesn't have an INNER JOIN to tblProperties and WHERE clause? I'm not good with sub queries, so it's getting kind of deep for me.

Do you think that may be slowing it down? How might the code be altered to add it? I took a stab and was getting syntax errors.

Also helpful, this is what the results look like:

 

[xenou]

Off the top of my head, I think in general Access works better (sometimes) if you query queries rather than having one big SQL query with lots of nested subqueries in it. I will frequently write a query that queries another query that (even) queries another query. In SQL Server I would probably instead use one query for all of that.

You may also consider writing a small procedure to put your data from _Group_Products into a temp table. Then you will indeed have a local table to query for the remainder of the task. If you go this route, think of it as a "stored procedure". There will be a few related things that all happen in turn: clear the temp table, insert it with new data, then run the rest of the queries that need to run. How you do it is up to you - it can be done by hand just running the queries, or you can put the logic into a form and kick it off with one (or more) buttons. Or you can code it into a procedure in a VBA module and just call your procedure.

Also if you haven't already done so, check your indexed fields in the tables. Fields that are used in WHERE criteria, in general, are candidates for indexing. This can have a big effect in some cases.