Query Question

Otacustes · July 14th, 2008, 08:55 AM

Hello all,

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:

Code:

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.

Any help is greatly appreciated.

Thanks

Any help is greatly appreciated

Kind regards
Ota

BrianWren · July 14th, 2008, 12:14 PM

Tip: When you add code [code...] to a post, it is rendered literally. You should edit your post to make the 1st line at least 2 lines...

Code:

Private Function Additions(existingData   As ADODB.Recordset, _
                           additionalData As ADODB.Recordset _
                          ) As ADODB.Recordset

I don't believe you can do this. You need to take the SQL the creates the recordset(s), and use that to open a new recordset. I just know of no way to treat an ADODB RecordSet as a table. It is much more akin to a specialized array.

Otacustes · July 14th, 2008, 01:13 PM

Quote:

quote:Originally posted by BrianWren
Tip: When you add code [code...] to a post, it is rendered literally. You should edit your post to make the 1st line at least 2 lines...

Code:

Private Function Additions(existingData   As ADODB.Recordset, _

Code:

                           additionalData As ADODB.Recordset _
                          ) As ADODB.Recordset

I don't believe you can do this. You need to take the SQL the creates the recordset(s), and use that to open a new recordset. I just know of no way to treat an ADODB RecordSet as a table. It is much more akin to a specialized array.

Damn!! :( The recordsets are created from an excel spreadsheet...what would you suggest is a good way to create a table that I can run an SQL script on?

Thanks

Any help is greatly appreciated

Kind regards
Ota

Old Pedant · July 14th, 2008, 05:37 PM

If you want to do a JOIN, there is no reason you can't do the join *FROM* the Excel spreadsheet(s).

That is, do *NOT* create two separate recordsets. Just modify the SQL you used to get the data form Excel so *IT* returns a single recordset.

On the other hand, the code you show there doesn't look like it wants to do a JOIN. Looks like you just want the UNION of the two results, no? Is the structure of the two recordsets identical or nearly so??? Same number of fields with same types or at least compatible types??

If so, you *COULD* disassociate each RS from its connection if you use a client side cursor and then set the data source of each to NOTHING. Then you could indeed choose one RS as the "master" and use AddNew on it as you added records from the other Recordset. It would be slow and clumsy compared to other ways, but it should work.

You could also do this in VB.NET (well, actually ADO.NET) by creating a DataSet and then using the data from the two queries to create DataTables and then (to a limited degree) you can relate the two DataTables and do filtering on the result. Or, again if this is actually a UNION that is needed, it's trivial to copy DataRows from one DataTable to another. But clearly *NOT* using ADODB.Recordset objects. You'd have to switch to ADO.NET.

But lay some more details on us. Start by discussing whether you need a JOIN or a UNION. And start by discussing why you can't just use a single query against Excel (if, indeed, you can't).

Otacustes · July 15th, 2008, 01:13 AM

The SQL statement was something just to get me started and indeed in the future will change so that it compares existing data to the additional data and return a recordset with what has been added - that was the plan anyway!!

I am not sure how you'd alter the SQL statement to get the information from the excel sheet; the information is contained in different positions on the sheet.

Hopefully I didn't misunderstand you. Thanks very much.

Any help is greatly appreciated

Kind regards
Ota

BrianWren · July 15th, 2008, 12:27 PM

[u]Please</u> edit your original post so that this internet form is not wider
than my screen!

Loop through the recordset, adding each record in turn to a table in Access, then
open that table in a recordset. (You can create a connection, through that connection
create a table, then use SQL INSERT statements with the connection to populate the
table.)

Old Pedant · July 15th, 2008, 01:24 PM

You just give us nothing to go on.

SHOW the two queries you are using to get the two recordsets.

SHOW the list of fields and types in each recordset.