Query Question

[otacustes]

I have a function which runs an SQL query and returns a recordset. This function takes two recordsets as arguments for the SQL statement.

So far I have this:

Private Function Additions(existingData As ADODB.Recordset, additionalData As ADODB.Recordset) As ADODB.Recordset
    Dim adoRs As ADODB.Recordset
    Dim SQL As String

    Set adoRs = New ADODB.Recordset

    SQL = "SELECT Additional.*, Existing.* FROM Additional, Existing;"

    adoRs.Open SQL
    adoRs.Sort = "PartNo"

    Set Additions = adoRs

    Set adoRs = Nothing
End Function

Clearly this is incorrect; the problem I am having is having is how do you change this code so that the SQL terms additional and existing refer to the two recordsets passed into the function? What I am trying to do is to run the query on the two recordsets passed into the function as if they were tables.

If this will never work then what is the best alternative to approach this?

Any help is greatly appreciated.

Thanks
All help is greatly appreciated
Kind regards
Ota

[Ron Weller]

You are absolutly correct. This is not going to work.
A Query is always performed against a database object, which a recordset is not.
A recordset is an object, ADO, DAO, etc., that is used to hold the results of a query. Think of it as a data structure, like a big array, or a collection. Just like you can't run an SQL statement against a variable in your program code because the sql is sent to the database and the database has no access to your variable in your code.
So your options become to combine the two original SQL statments into one that retreives all the records that you need. Basically using one large SQL Statement.
Or to save each of the queries as a view, or if you are using MS Access, you can save them as queries. At this point these SQL Statements become database objects that you can query against. You use the names you saved them with, just like they were tables names.