Query a Recordset

[mjk9564]

Is it possible to query a recordset? I have a query that I need limit the results on. Maybe you can think of another way to do it.

Dim rsLimit As New ADODB.Recordset
Dim rslimit2 As New ADODB.Recordset
Dim strSQLLimit As String
Dim strSQLPass As String

'Limit the results with the bn5102 ingredient
strSQLLimit = "SELECT A.RecipeID, A.RevisionNumber " & _
"FROM tblRecipes A INNER JOIN tblIngredients B " & _
"ON A.RecipeID = B.RecipeID " & _
"AND A.RevisionNumber = B.RevisionNumber " & _
"WHERE (B.IngredientID = 17) And (A.CategoryID = 1)"

rslimit.open strlimit, conrecipemgr

strSQLPass = "SELECT RecipeID FROM " & rslimit.source & "WHERE IngredientID = 19"

rslimit2.open strSqlPass, conrecipemgr

********************************************
This is what I have so far I know it doesn't work. The only other way I can think of is to create a view in SQL Server from the first query and then query that view and delete it when finished. If anyone has a better I idea I would appreciate it.

Matt

 

[MarkSweetland]

You can't query the recordset directly, but you can filter the recordset based on a condition:

rslimit.Filter = "IngredientID = 19"




Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[CCLINT]

You can create a recordset clone so that the original recordset doesn't also get filtered:

Dim rsClone As ADODB.Recordset
Set rsClone = rslimit.Clone
rsClone.Filter = "IngredientID = 19"