Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Query and Local Tables in Excel
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!!! -- Kevin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Query and Local Tables in Excel
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. -- Rob van Gelder - http://www.vangelder.co.nz/excel "Kevin" wrote in message ... 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!!! -- Kevin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Query and Local Tables in Excel
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 "Kevin" wrote: 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!!! -- Kevin |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Query and Local Tables in Excel
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 "ben" wrote: 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 "Kevin" wrote: 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!!! -- Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Local OLAP cubes in Excel/MS Query | New Users to Excel | |||
Excel 2000 on WTS 2003 and local Excel links don't update | Excel Discussion (Misc queries) | |||
Stoping users from saving tables to their local machines... | Excel Discussion (Misc queries) | |||
Two Excel tables in MS Query | Excel Discussion (Misc queries) | |||
Reference styles and local/non-local formulae - international problems. | Excel Programming |