VBA Excel Recordset Joins

[ineedhelplease]

I have an Excel project that desperatly needs a way to join recordsets; this is needed for speed. What I have read so far is it cannot be done.

Currently my project opens several recordersets in Excel VBA and I have to cycle through each to create a final recordset used to update Excel worksheets. I am not able to do this in MS Access or any other application as each current recordset created from various sources; the sources are an active Excel worksheet, an Oracle table and a Teradata table.

I believe it would be much faster to just join several recordsets in Excel VBA to build a final recordset. Below is a very simple example of what I am speaking of. Is there anyway to join the two example recordsets and open the results into a third? Any help would be appreciated.


Sub testrecordsetjoin()


Dim r1, r2, r3 As ADODB.Recordset
Dim rnSQL As String

' create r1 the first recordset for testing excel vba recordset joins
Set r1 = New ADODB.Recordset
With r1
With .Fields
.Append "CITY", adLongVarChar, 20
End With
.Open
End With

' append data to the first recordset for testing
With r1
.AddNew
!CITY = "Seattle"
.Update
.AddNew
!CITY = "Boston"
.Update
.AddNew
!CITY = "Chicago"
.Update
End With

' create r2 the second recordset for testing excel vba recordset joins
Set r2 = New ADODB.Recordset
With r2
With .Fields
.Append "oCITY", adLongVarChar, 20
End With
.Open
End With

' append test data to the second test recordset
With r2
.AddNew
!oCITY = "Renton"
.Update
.AddNew
!oCITY = "Boston"
.Update
.AddNew
!oCITY = "Chicago"
.Update
End With

' create r3 the third recordset for testing excel vba recordset joins, this will hold the qry results
Set r3 = New ADODB.Recordset

' sql string for r3
rnSQL = "SELECT r1.CITY FROM r1 WHERE r1.CITY = r2.oCITY"
' open rnsql; no connection
r3.Open rnSQL 'problem: expecting a connection string - none to provide in this example

End Sub

 

[SkipVought]

hi,

I am not able to do this in MS Access or any other application as each current recordset created from various sources; the sources are an active Excel worksheet, an Oracle table and a Teradata table.

That is not necessarily true. You could LINK your Excel workbook(s) & Oracle table in MS Access. I do not know about Teradata, as I have no experience with it. I know that I have linked Excel workbook/Sheets, Oracle tables, & DB2 tables in on MS Access database.

You could also, query each of the tables in separates sheets, if the resultsets will fit, and then join finally using a query to join the sheets containing the resultsets. I have dons this also.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}