Interesting, thanks.

Can I use the recordset from Oracle in a left join without putting it on a
sheet, thereby eliminating the need to create a new sheet?

If I do create a new sheet with the Oracle rs, why not use array formulae to
pull the information over - too slow? I'm going to test this one myself, but
if you know the answer, I'll take it.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"onedaywhen" wrote in message
om...
Happy to oblige, Dick.

To join the Oracle and Excel tables they need to be in the same
location. It would probably be better to do the work in Oracle (i.e.
import data from Excel into Oracle) but I'll do the work in Excel.
Open a connection using Jet OLEDB and the Excel workbook as the data
source (I won't try and guess an Oracle connection string so I'll
infer a DSN). Execute this SQL statement (no recordset generated)
against the Excel connection:

SELECT KeyCol1, KeyCol2, Value
INTO NewTempTable
FROM
[ODBC;DSN=MyOracleDB;].MyOracleTable

Assume the Excel table is on Sheet1 and the Oracle table is now in the
same workbook on a new sheet called NewTempTable. For ease of
reference, use this data:

Excel table: Sheet1:
[A1:D1]=array("Name","KeyCol1","KeyCol2","Value")
[A2:C2]=array("Norarules",1,1)
[A3:C3]=array("Livehulas",1,2)
[A4:C4]=array("Regisaver",1,3)
[A5:C5]=array("Hevitoxic",1,4)
[A6:C6]=array("Domatican",2,1)
[A7:C7]=array("Pipesagap",2,2)
[A8:C8]=array("Luxasonic",2,3)
[A9:C9]=array("Katewudes",2,4)

Oracle table in now Excel: NewTempTable:
[A1:C1]=array("KeyCol1","KeyCol2","Value")
[A2:C2]=array(1,1,18)
[A3:C3]=array(1,2,24)
[A4:C4]=array(1,3,33)
[A5:C5]=array(1,4,52)
[A6:C6]=array(1,5,59)
[A7:C7]=array(1,6,60)
[A8:C8]=array(1,7,63)
[A9:C9]=array(1,8,88)

To generate the required data, create a recordset by executing this
SQL statement against the Excel connection:

SELECT T1.Name,T1.KeyCol1,T1.KeyCol2,T2.Value
FROM [Sheet1$] T1 LEFT JOIN [NewTempTable$] T2
ON T1.KeyCol1=T2.KeyCol1 AND T1.KeyCol2=T2.KeyCol2
ORDER BY T1.KeyCol1,T1.KeyCol2

To make the rowset visible, try this:

SELECT T1.Name,T1.KeyCol1,T1.KeyCol2,T2.Value
INTO GeneratedRowset
FROM [Sheet1$] T1 LEFT JOIN [NewTempTable$] T2
ON T1.KeyCol1=T2.KeyCol1 AND T1.KeyCol2=T2.KeyCol2
ORDER BY T1.KeyCol1,T1.KeyCol2

and view the new sheet called GeneratedRowset.

The left join means all the Excel rows are returned and Value returned
from the lookup table where one exists otherwise it is null. Sheet1
can now be updated as appropriate. In my example, I can generate the
recordset in the same order as the original table, therefore I could
use CopyFromRecordset to replace the whole table.

Beyond this it gets hard to generalize. The Oracle table may be too
large to fit simply into Excel or that network guy (<g) may not be
happy with whole tables flying around the wires. If it's not crucial
to trigger the process from Excel, it may be simpler to link the
tables in MS Access and do the join from there.

--

"Dick Kusleika" wrote in message
...
Jamie

Ah, the joys of the being THE network admin (I never complain to
myself.).<g

Would you mind sketching out how you would set that up? I'd be
interested
in learning a better way. Thanks.


--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"jamieuk" wrote in message
om...
Using Dick's function as a UDF in a formula in R1 and copying down to
R65536 would result in 65536 queries, 65536 trips across the network,
etc (and the connection is never closed).

The solution obviously works for you but if you get performace issues
(or complaints from your network admin or DBA!) bear in mind it is
possible to do this in one hit: *one* query to create a recorset for
all rows, then use update the spreadsheet using CopyFromRecordset or
some other method.

--