Nesting Queries

[jtmach]

I am trying to pull in a huge recordset from across a network. I would like to be able to pull in a record set and then use it in subsequent queries as I see fit. Here is what I am using. I have taken out any data that I don't think is relevent if you need more just ask.

Public Function RunSelectQuery(SQLStatement As String)
    'Run a select query
    Dim DB As Database
    Dim Qry As QueryDef
    Dim RST As Recordset
    Set DB = CurrentDb
    Set Qry = DB.CreateQueryDef("", SQLStatement)
    Set RST = Qry.OpenRecordset()
    
    Set RunSelectQuery = RST
End Function

Private Function Test()
    Dim rst1 As Recordset
    Set rst1 = RunSelectQuery("SELECT * FROM transferInfo")
    
    Dim rst2 As Recordset
    Set rst2 = RunSelectQuery("SELECT * FROM " & rst1)
End Function

When I try to reference the rst1 in the second query I am getting a type mismatch error, but I don't know why. Any help you could give would be appreciated. The hardest questions always have the easiest answers.

 

[llocklin]

While the RunSelectQuery can return a reference to a recordset (since being unspecified, it's a variant), I would have thought that the recordset it is referencing is destroyed as soon as the code exits RunSelectQuery since the recordset created is local to the function.

If you want to use a function to return a recordset based on a query string passed, then pass the recordset variable as well (the default passing convention is ByRef).

Public Function RunSelectQuery(RST as RecordSet, SQLStatement As String)
'Run a select query

Dim DB As Database
Set DB = CurrentDb
Set RST = DB.OpenRecordset(SQLStatement)

End Function

Private Function Test()
Dim rst1 As Recordset
RunSelectQuery(rst1, "SELECT * FROM transferInfo&quot

Dim rst2 As Recordset
Set rst2 = rst1.OpenRecordset("New query here&quot
End Function


The second recordset will be a subset of the first.

Luther

 

[jtmach]

That is interesting, but how do your phrase your second SQL statement. What would the FROM look like. The hardest questions always have the easiest answers.

 

[llocklin]

Well, you caught me in a lie. I don't often open a recordset from a recordset (since it's just as easy to just create another recordset...).

Set the filter property of the 1st recordset and then when the second recordset is created it will implement the filter. Here's the rewritten example:

Function RunSelectQuery(RST as RecordSet, SQLStatement As String)
'Run a select query

Dim DB As Database
Set DB = CurrentDb
Set RST = DB.OpenRecordset(SQLStatement, dbOpenDynaSet)

End Function

Private Function Test()
Dim rst1 As Recordset
Dim rst2 As Recordset

RunSelectQuery(rst1, "SELECT * FROM transferInfo&quot
rst1.Filter = "INVOICE = '12345'"
Set rst2 = rst1.OpenRecordset
End Function

As a side note, I wrote the examples this way to keep the format you specified. I usually don't call a procedure to create a recordset.

Hopefully, I've gotten it right this time.

Luther

 

[Mirak]

Well jtmach,
Instead of creating two recordsets, I would use nested SQL statements to pull out the records that I want. SQL is very powerful and can surely extract specific data. You just need to know how to nest it. I am not sure exactly what type of data you want. But you can nest several sql statements until you get what you. For example:

SELECT tblOne.field1, tblTwo.field2
FROM tblOne, tblTwo
WHERE tlbOne.PrimaryKeyField = tblTwo.ForeignKeyField
AND tblOne.field1 = (SELECT field1
FROM tblOne
WHERE my_criteria_on_other_field
BETWEEN (condition1) AND (condition2)
)
AND tblTwo.field2 >
(SELECT field2
FROM tblTwo
WHERE my_criteria_on_other_field
NOT IN (condition1,condition2,condition3)
)

Essentially, SQL is a powerful workhorse, and I suggest that you exploit it and create one recordset instead of dealing with multiple recordsets when you don't need to.
In my opinion its easier to Requery, Refresh, Open and Close on one Recordset than having to do it on several.

Mirak

__________