VB Forum / Database Access / September 2003

I need to run SQL on two recordsets. For example:

1. Recordset 1 - [CUSTOMER]
Recordset 1 is to capture [Customers] table

2. Recordset 2 - [ITEMS ORDERED]
Recordset 2 is to capture [Items Ordered] table.

3. Recordset 3 - SQL results on Recordset 1 and Recordset 2
Recordset 3 is made using SQL query on Recordset 1 and Recordset 2.

This technique (if technically feasible) would be very useful in breaking
long
and complex SQL that runs on Oracle Performance Analyser.
At work, I use such lengthy SQLs for portfolio segmentation and analysis.
Now
I have been thinking about running SQLs using ADO directly on Excel's VBA,
which
would enable me to eliminate daunting copy (from Oracle) & paste (into
Excel) works.

The sample code is as follows:

Sub TestSQL_On_Recordsets()
'   PLEASE SCROLL DOWN TO SEE WHERE IS THE PROBLEM.

Dim cnn1 As New ADODB.Connection

Dim rstCust As ADODB.Recordset
Dim rstItmOrd As ADODB.Recordset

Dim sqlCust As String
Dim sqlItmOrd As String

Dim strCon As String

'   CustomerTransaction.mdb contains two tables: Customers and Items_Ordered

   strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=E:\temp\CustomerTransaction.mdb;"

   cnn1.Open strCon

'   CUSTOMERS - RECORDSET CREATION
'   -------------------------------

'   Create fresh recordset for customer
   Set rstCust = New ADODB.Recordset

'   Define SQL for selecting all records from customers
   sqlCust = "Select * from customers"

'   Open recordset - customers
   rstCust.Open sqlCust, cnn1, adOpenStatic, adLockReadOnly

'   ITEMS ORDERED - RECORDSET CREATION
'   ----------------------------------

'   Create fresh recordset for customer
   Set rstItmOrd = New ADODB.Recordset

'   Define SQL for selecting all records from Items_Ordered
   sqlItmOrd = "Select * from Items_ordered"

'   Open recordset - customers
   rstItmOrd.Open sqlItmOrd, cnn1, adOpenStatic, adLockReadOnly

'   TRY TO CONNECT BETWEEN TWO RECORDSETS TO CREATE A NEW RECORDSET
'   ---------------------------------------------------------------

Dim rstSQL As New ADODB.Recordset
Dim sqlCustItm As String

'   THIS IS WHERE THE PROBLEM IS:
'   PLEASE HELP ME ON THE CODE BELOW
'   ---------------------------------

'   Define SQL for selecting records from rstCust and rstItmOrd
   sqlCustItm = "SELECT A.customer_id, A.fullname, SUM(B.quantity *
B.price)" & _
                "FROM rstCust AS A, rstItmOrd as B" & _
                "WHERE a.customer_id = b.customer_id" & _
                "GROUP BY a.customer_id, a.fullname"

'   Open recordset
   rstSQL.Open sqlCustItm        ' ERROR IS GENERATED HERE

END SUB

As can be seen, rstCust and rstItmOrd are treated as table in the last SQL.
Unfortunately, this just does not work. Could you please help me how to
workaround this problem?
Thanks.
.