Update Query and Local Tables in Excel

[Guest]

I have a group of Access queries that I run in sequence across an ODBC
connection against an Oracle database. The first two are update queries that
create local Access tables that are then queried by the remaining queries in
the group. In other words, the first two queries query Oracle and create two
local tables. Subsequent queries query the two local tables to further refine
the result set.

I am trying to migrate this whole operation to Excel since this is
ultimately where the result set is used. I can query Oracle through ODBC from
Excel. No problem. But now I'm stuck. I somehow need to create a "local
table" of the result set in Excel that I can later query. Or is there a way
for me to query the resulting recordset itself? I've read through many of the
postings on the site and consulted the books I have and the help files, but I
haven't been able to pull all of the pieces together.

Can anyone point me in the right direction?

Thanks in advance!!!

 

[Rob van Gelder]

There are methods for treating an Excel sheet as a Table which can be
queried through ODBC Jet.
This is probably the preferred method by some.


Another way is to configure the querytable to fill adjacent formulas.
Use formulas to modify your results. Some of the formulas might contain 1 or
0 depending on whether you want those results included in the next table.

The next table is simply a pivot table. based on your queried results.
Use Page filters to exclude the formulas returning 0.

 

[Guest]

I think what you are looking for is creating a table in excel, this is done
by naming a specified range. eg...

ActiveWorkbook.Names.Add Name:="destin2", RefersToR1C1:="=SHEET1! _
R1C147:R10:C200"

this in excel workbooks as a "table" for queries
ben

 

[Guest]

Ben:

So if I'm understanding properly, I would be able to name the range and then
run a query such as:

SELECT * INTO destin2 FROM MyOracleTableODBC

Is that correct?

Can I subsequently refer to this named range in a query, such as:

SQL_Text = "SELECT * FROM destin2 WHERE Something = SomethingElse"
MyRecordset.Open SQL_Text, Cnn, adOpenStatic

Kevin