Link to home
Start Free TrialLog in
Avatar of atprato
atprato

asked on

Join two recordsets in VBA in memory

I have two recordsets filled with data in excel VBA: rs1 and rs2.  I want to join them to create one rs3 in VBA and the dump that recordset to a spreadsheet.  I've read an SQL join can not be done to two recordsets in VBA memory.  Is that true?  Is there anyway to borrow some tools from Access to accomplish this join?  I don't want to open Access, I just want to use some objects and methods to make the join in my Excel Visual Basic Editor.  
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Can't you just dump one recordset into the worksheet, then the other? You can't, as far as I know, join them in code.
create a union query

select * from table1
union all
select * from table2

Can you not create rs3 at the same time as rs1 and rs2?

It should be fairly straightforward. Can we see the code you currently have?
Avatar of atprato
atprato

ASKER

Rorya:
I am getting them from the spreadsheet.  I currently do a time consuming loop in VBA to join spreadsheet data and I'm searching for a faster way without going all the way to Access.  Though I am hoping to borrow some objects and methods from access to accomplish my goal in Excel VBA.

Capricorn1:
Table1 and Table2, are those my recordsets?  Your code string does not specify the key in each table for the join, so I don't understand how it works?  What is the name of the resulting recordset?

TZych:
My thinking was that a join would be very fast as opposed to manually creating rs3 as I broght the data for the other two in.  Do you have a fast way of joining 50,000 records to 50,000 records?

jimpen:
I've attached my code below.  Basically all it does is loads two spreadsheet tables into two recordsets.  My hope was that once I got all the data into structured memory I could somehow join the two recordsets like one would join two tables.  SQL or otherwise.  


Sub RsJoin()
Dim RsA As New ADODB.Recordset
Dim RsB As New ADODB.Recordset
Dim TableAkey As Range
Dim TableBkey As Range
Dim cel As Range
 
Set TableAkey = Sheets("Table A").Range("A2", Sheets("Table A").Range("A2").End(xlDown))
Set TableBkey = Sheets("Table B").Range("A2", Sheets("Table B").Range("A2").End(xlDown))
 
With RsA
    .Fields.Append "SN", adChar, 15
    .Fields.Append "Qty_Received", adInteger, 10
    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
    .Open
End With
 
With RsB
    .Fields.Append "SN", adChar, 15
    .Fields.Append "Qty_Issued", adInteger, 10
    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
    .Open
End With
 
For Each cel In TableAkey
    RsA.AddNew
    RsA!SN = cel.Value
    RsA!Qty_Received = cel.Offset(0, 1).Value
    RsA.Update
Next cel
RsA.MoveFirst
Set cel = Nothing
 
For Each cel In TableBkey
    RsB.AddNew
    RsB!SN = cel.Value
    RsB!Qty_Issued = cel.Offset(0, 1).Value
    RsB.Update
Next cel
RsB.MoveFirst
Set cel = Nothing
 
Set RsA = Nothing
Set RsB = Nothing
Set cel = Nothing
Set TableAkey = Nothing
Set TableBkey = Nothing
 
End Sub

Open in new window

You can use ADO with a union query to select all of the records from each sheet into a single recordset assuming they have the same columns?
My point was since you are using ADO to create rs1 and rs2, create rs3 instead using ADO.

This example performs an inner join on two tables named "rng1" and "rng2" from the same worksheet.

Sub AdoTester()

    Dim rs As ADODB.Recordset
    Dim strConn As String
    Dim strSql As String
     
    strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=D:\Data\AdoTest.xls;Extended Properties=""Excel 8.0;HDR=Yes"";"
    strSql = "SELECT [rng1].* FROM [rng1] INNER JOIN [rng2] ON [rng1].[Field1] = [rng2].[Field3];"
   
    Set rs = New ADODB.Recordset
    rs.Open strSql, strConn, adOpenForwardOnly, adLockReadOnly, adCmdText
    If Not rs.EOF Then
        ThisWorkbook.Worksheets(1).Cells.ClearContents
        ThisWorkbook.Worksheets(1).Range("A1").CopyFromRecordset rs
    Else
        MsgBox "no records"
    End If
   
    rs.Close
    Set rs = Nothing

End Sub

Tim
I think he is looking to pull a Left/right/outer/inner JOIN type query against two spreadsheets as opposed to a union query.

The syntax in Access/SQL would be something like:
-----------------------
SELECT TABLEA.Keyfield, TABLEA.Col2, TABLEA.Col3, TABLEB.Col2, TABLEB.Col4
FROM TableA LEFT JOIN TABLEB
      ON TABLEA.Keyfield = TABLEB.Keyfield
-----------------------

I don't really have enough Excel handling to really beat on it from his code
Avatar of atprato

ASKER

rorya:
Sorry, I'm new to the terminology.  Sounds like by union you mean stack vertically?  If so, that is not what I am looking for.  I am trying to Join horizontally.  Like an access inner join.  Not all my fields are the same, just the key field.  I'm open to other objects if I'm limiting myself with the recordset?  Anything I can load up with data and then join quickly works for me.
That should be OK too - see Tim's post above.
Rory
Avatar of atprato

ASKER

Tim, wow, I had no idea I could do an SQL statement to a spreadsheet.  I've got the code all set up to my work book but I am having trouble with the SQL statment.  How do I call out the ranges and fields?  Please see my code.
TableA has two column headings:
Item_Number and Qty_Received

TableB has two column headings
Item_Number and Qty_issued
Sub AdoTester()
 
    Dim rs As ADODB.Recordset
    Dim strConn As String
    Dim strSql As String
    Dim TableA As Range
    Dim TableB As Range
     
    Set TableA = Sheets("Table A").Range("A1", Sheets("Table A").Range("A1").End(xlDown).Offset(0, 1))
    Set TableB = Sheets("Table B").Range("A1", Sheets("Table B").Range("A1").End(xlDown).Offset(0, 1))
    
    strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=G:\VBA Training\ADO test.xls;Extended Properties=""Excel 8.0;HDR=Yes"";"
    strSql = "SELECT [rng1].* FROM [rng1] INNER JOIN [rng2] ON [rng1].[Field1] = [rng2].[Field3];"
 
    
    Set rs = New ADODB.Recordset
    rs.Open strSql, strConn, adOpenForwardOnly, adLockReadOnly, adCmdText
    If Not rs.EOF Then
        ThisWorkbook.Worksheets("Join").Cells.ClearContents
        ThisWorkbook.Worksheets("Join").Range("A1").CopyFromRecordset rs
    Else
        MsgBox "no records"
    End If
    
    rs.Close
    Set rs = Nothing
    Set TableA = Nothing
    Set TableB = Nothing
 
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of TZych
TZych
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If your sheets contain no other data, then you can simply refer to the worksheets as tables and retrieve all data that way without worrying about finding the last row. If you are performing a join, any included blank rows should not be an issue anyway.
FWIW
Avatar of atprato

ASKER

Very Very nice.  I'll have to read up on the bug because I did not notice it when I ran it in one open workbook.

That solution is so interesting you've certainly earned the points.  But I still can't do a join in memory in the VBE of Excel.  The one situation I run into where I will still need to do this is when I need to join a large Qry, say 300K records to a small list kept on a spreadsheet, say 3K.  My approach had been to bring the 300K down from the ERP to a recordset, then push the 3K up to another recordset, then join the two to a manageable size, then dump the resulting 3K back to the spreadsheet.  Hence my original question of how to join to recordsets in memory.

Can't I borrow some access objects to load my data to, do the join and dump back to excel?  Unless there is some way I can use this new SQL on excel trick over multiple sheets.  That is, get it to consider six sheets of data as on table (for my set of 300K records)?
You can use an Excel worksheet as a linked table in Access. Or if you had your data on 6 worksheets, you can use a UNION query to join them all up. Probably easier in Access though, IMO.
Avatar of atprato

ASKER

Rorya:
The Union on the six sheets sounds interesting since the SQL is running so fast.  But I am new to SQL.  What would that statement look like to join my 3K table in excel to the Union of of the six tables in one statement so that the result is an innerjoin and has all the feilds from the union and all the feilds from the 3K table?
>> bring the 300K down from the ERP to a recordset

What is the DB? Can you do an ODBC link to the ERP DB directly? That could solve some of your problems.