Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying a database for values in each row
Howdy.
I have a worksheet where each row has several columns with values. I want to take these column values and look up a value from an external database. I also don't want to create a query for each row (since there are many thousands of rows). I know how to get a query to return a value for a single row, but is there a way to get a query to return a value for each row and place the result in a column at the end of the row whose values are being matched. Thanks in advance for your help. Stephen |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying a database for values in each row
In database terms, you want to LEFT JOIN all the rows in your Excel
range (table on the left) to those in the your external database (table on the right) or a row of nulls if there is no match. The join would look something like this: SELECT T1.Col1, T1.Col2, T1.Col3, T2.LookupCol As NewCol FROM MyExcelData T1 LEFT JOIN MyExternalData T2 ON T1.Col1=T2.Col1 AND T1.Col2=T2.Col2 AND T1.Col3=T2.Col3 I've never known a RDBMS allow you to JOIN tables from different data sources on the fly i.e. without previously linking servers (SQL Server) or using linked tables (MS Access). You need both tables in the same 'place'. Also, in database terms, you are talking about appending a new column to an existing table, which is not something normally done on the fly with a query! So it makes me wonder: is this a one off or something you will be doing regularly in an Excel application? If it's a one off, you are probably best off importing or linking the Excel data within the RDBMS, create a new table locally using a LEFT JOIN, then updating the original Excel source. If it was a regular thing, it would be difficult to do all this in code from Excel if you wanted to repeat regularly. Post back with some more details e.g. the external database (MS Access, SQL Server, another Excel workbook etc), what your data looks like (Excel and external), how often you plan to do this, whether to run from code, etc. -- "Stephen Goldfinger" wrote in message ... Howdy. I have a worksheet where each row has several columns with values. I want to take these column values and look up a value from an external database. I also don't want to create a query for each row (since there are many thousands of rows). I know how to get a query to return a value for a single row, but is there a way to get a query to return a value for each row and place the result in a column at the end of the row whose values are being matched. Thanks in advance for your help. Stephen |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying a database for values in each row
Thanks for taking the time to reply. Your assessment is right on the
mark. Here is an example (I hope they line up well.) Lookup Lookup Column 1 Column 2 Result -------- -------- ------ A1 A2 R1 B1 B2 R2 C3 C3 R3 Explanation: Given the combination of values (A1 and A2), I can determine the SINGLE result value, R1. Each combination of Column 1 and Column 2 will return a singleton value. Given what I know of Excel, I can write a query that takes the values of the first row (i.e. A1 & A2) to then go get the resulting value R1. I can then write another query for (B1 & B2) to get me R2. I don't want to have to write multiple queries. I want one query (or a function) that can take the Column 1 and Column 2 values for each row and get me the Result value for that particular row. I hope this clarifies the question. Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying a database for values in each row
Stephen
Here's how I'd do it: First create a module level variable for an ADODB connection object. Set a reference to Microsoft ActiveX Data Object x.x under Tools - References. Next, create a sub that opens the ADODB connection. Create a sub that closes the ADODB connection which you should call from your Workbook_BeforeClose event. Finally, create function that creates a recordset and pull the data out of there. Here's an example. Dim Conn As ADODB.Connection Sub EstablishConnection() Dim MyConn As String Set Conn = New ADODB.Connection MyConn = "DSN=MS Access 97 Database;DBQ=C:\Dick\db1.mdb;" MyConn = MyConn & "DefaultDir=C:\Dick;DriverId=281;" MyConn = MyConn & "FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" Conn.Open MyConn End Sub Function GetValue(Rng1 As Range, Rng2 As Range) As Variant Dim MySql As String Dim rs As ADODB.Recordset If Conn Is Nothing Then EstablishConnection End If MySql = "SELECT `Table 1`.number, `Table 1`.name, `Table 1`.TheDate " MySql = MySql & "FROM `C:\Dick\db1`.`Table 1` `Table 1` " MySql = MySql & "WHERE (`Table 1`.number='" & Rng1.Value & "') AND " MySql = MySql & "(`Table 1`.name='" & Rng2.Value & "')" Set rs = Conn.Execute(MySql) rs.MoveFirst GetValue = rs.Fields(2).Value End Function Sub TermConnection() Conn.Close Set Conn = Nothing End Sub You could create an destroy the ADODB connection inside of the function, but that will increase your processing time and may slow you down. I think it's better to open the connection once and close it once and leave it open while the workbook is open. You call the function like =GetValue(A1,B1) and the recordset returned inside the function should contain the proper value, because as you say, there is only one combination of the first two fields in the database. Give it a try and let me know if you need any clarification. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Stephen Goldfinger" wrote in message ... Thanks for taking the time to reply. Your assessment is right on the mark. Here is an example (I hope they line up well.) Lookup Lookup Column 1 Column 2 Result -------- -------- ------ A1 A2 R1 B1 B2 R2 C3 C3 R3 Explanation: Given the combination of values (A1 and A2), I can determine the SINGLE result value, R1. Each combination of Column 1 and Column 2 will return a singleton value. Given what I know of Excel, I can write a query that takes the values of the first row (i.e. A1 & A2) to then go get the resulting value R1. I can then write another query for (B1 & B2) to get me R2. I don't want to have to write multiple queries. I want one query (or a function) that can take the Column 1 and Column 2 values for each row and get me the Result value for that particular row. I hope this clarifies the question. Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying a database for values in each row
Thanks for the great answer. Generally, it looks like it will fit the
bill. Of course, there's one hitch. I am trying to access an Oracle 9i database and I don't know how to change the MyConn string appropriately for Oracle. I don't know where to look for help documentation otherwise I wouldn't waste any more of your time. Any ideas of either where to look or how to change it? Thanks in advance. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying a database for values in each row
Stephen
Here's my method for getting the connection string and the sql: Set up an External Data Table manually in Excel (Data - Get External Data - New Database Query). Set it up as close as you can to how you'll want it when you do it through code. Then go to the Immediate Window and type ?Sheet1.QueryTables(1).Connection ?Sheet1.QueryTables(1).CommandText to give you the proper syntax. Remove the DSN from the beginning of the connection string if it's there. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Stephen Goldfinger" wrote in message ... Thanks for the great answer. Generally, it looks like it will fit the bill. Of course, there's one hitch. I am trying to access an Oracle 9i database and I don't know how to change the MyConn string appropriately for Oracle. I don't know where to look for help documentation otherwise I wouldn't waste any more of your time. Any ideas of either where to look or how to change it? Thanks in advance. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying a database for values in each row
I understand your problem. To recap, the solution is to join your
Excel table to your external database table then update your Excel table with the generated recordset. But first you will have to get both tables in the same place. I can elaborate but first tell me: 1) the external database (MS Access, SQL Server, another Excel workbook etc) 2) how often you plan to do this e.g. is it a one-off exercise or a regular application function. -- Stephen Goldfinger wrote in message ... Thanks for taking the time to reply. Your assessment is right on the mark. Here is an example (I hope they line up well.) Lookup Lookup Column 1 Column 2 Result -------- -------- ------ A1 A2 R1 B1 B2 R2 C3 C3 R3 Explanation: Given the combination of values (A1 and A2), I can determine the SINGLE result value, R1. Each combination of Column 1 and Column 2 will return a singleton value. Given what I know of Excel, I can write a query that takes the values of the first row (i.e. A1 & A2) to then go get the resulting value R1. I can then write another query for (B1 & B2) to get me R2. I don't want to have to write multiple queries. I want one query (or a function) that can take the Column 1 and Column 2 values for each row and get me the Result value for that particular row. I hope this clarifies the question. Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying a database for values in each row
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. -- "Dick Kusleika" wrote in message ... Stephen Here's how I'd do it: First create a module level variable for an ADODB connection object. Set a reference to Microsoft ActiveX Data Object x.x under Tools - References. Next, create a sub that opens the ADODB connection. Create a sub that closes the ADODB connection which you should call from your Workbook_BeforeClose event. Finally, create function that creates a recordset and pull the data out of there. Here's an example. Dim Conn As ADODB.Connection Sub EstablishConnection() Dim MyConn As String Set Conn = New ADODB.Connection MyConn = "DSN=MS Access 97 Database;DBQ=C:\Dick\db1.mdb;" MyConn = MyConn & "DefaultDir=C:\Dick;DriverId=281;" MyConn = MyConn & "FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" Conn.Open MyConn End Sub Function GetValue(Rng1 As Range, Rng2 As Range) As Variant Dim MySql As String Dim rs As ADODB.Recordset If Conn Is Nothing Then EstablishConnection End If MySql = "SELECT `Table 1`.number, `Table 1`.name, `Table 1`.TheDate " MySql = MySql & "FROM `C:\Dick\db1`.`Table 1` `Table 1` " MySql = MySql & "WHERE (`Table 1`.number='" & Rng1.Value & "') AND " MySql = MySql & "(`Table 1`.name='" & Rng2.Value & "')" Set rs = Conn.Execute(MySql) rs.MoveFirst GetValue = rs.Fields(2).Value End Function Sub TermConnection() Conn.Close Set Conn = Nothing End Sub You could create an destroy the ADODB connection inside of the function, but that will increase your processing time and may slow you down. I think it's better to open the connection once and close it once and leave it open while the workbook is open. You call the function like =GetValue(A1,B1) and the recordset returned inside the function should contain the proper value, because as you say, there is only one combination of the first two fields in the database. Give it a try and let me know if you need any clarification. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Stephen Goldfinger" wrote in message ... Thanks for taking the time to reply. Your assessment is right on the mark. Here is an example (I hope they line up well.) Lookup Lookup Column 1 Column 2 Result -------- -------- ------ A1 A2 R1 B1 B2 R2 C3 C3 R3 Explanation: Given the combination of values (A1 and A2), I can determine the SINGLE result value, R1. Each combination of Column 1 and Column 2 will return a singleton value. Given what I know of Excel, I can write a query that takes the values of the first row (i.e. A1 & A2) to then go get the resulting value R1. I can then write another query for (B1 & B2) to get me R2. I don't want to have to write multiple queries. I want one query (or a function) that can take the Column 1 and Column 2 values for each row and get me the Result value for that particular row. I hope this clarifies the question. Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying a database for values in each row
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. -- "Dick Kusleika" wrote in message ... Stephen Here's how I'd do it: First create a module level variable for an ADODB connection object. Set a reference to Microsoft ActiveX Data Object x.x under Tools - References. Next, create a sub that opens the ADODB connection. Create a sub that closes the ADODB connection which you should call from your Workbook_BeforeClose event. Finally, create function that creates a recordset and pull the data out of there. Here's an example. Dim Conn As ADODB.Connection Sub EstablishConnection() Dim MyConn As String Set Conn = New ADODB.Connection MyConn = "DSN=MS Access 97 Database;DBQ=C:\Dick\db1.mdb;" MyConn = MyConn & "DefaultDir=C:\Dick;DriverId=281;" MyConn = MyConn & "FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" Conn.Open MyConn End Sub Function GetValue(Rng1 As Range, Rng2 As Range) As Variant Dim MySql As String Dim rs As ADODB.Recordset If Conn Is Nothing Then EstablishConnection End If MySql = "SELECT `Table 1`.number, `Table 1`.name, `Table 1`.TheDate " MySql = MySql & "FROM `C:\Dick\db1`.`Table 1` `Table 1` " MySql = MySql & "WHERE (`Table 1`.number='" & Rng1.Value & "') AND " MySql = MySql & "(`Table 1`.name='" & Rng2.Value & "')" Set rs = Conn.Execute(MySql) rs.MoveFirst GetValue = rs.Fields(2).Value End Function Sub TermConnection() Conn.Close Set Conn = Nothing End Sub You could create an destroy the ADODB connection inside of the function, but that will increase your processing time and may slow you down. I think it's better to open the connection once and close it once and leave it open while the workbook is open. You call the function like =GetValue(A1,B1) and the recordset returned inside the function should contain the proper value, because as you say, there is only one combination of the first two fields in the database. Give it a try and let me know if you need any clarification. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Stephen Goldfinger" wrote in message ... Thanks for taking the time to reply. Your assessment is right on the mark. Here is an example (I hope they line up well.) Lookup Lookup Column 1 Column 2 Result -------- -------- ------ A1 A2 R1 B1 B2 R2 C3 C3 R3 Explanation: Given the combination of values (A1 and A2), I can determine the SINGLE result value, R1. Each combination of Column 1 and Column 2 will return a singleton value. Given what I know of Excel, I can write a query that takes the values of the first row (i.e. A1 & A2) to then go get the resulting value R1. I can then write another query for (B1 & B2) to get me R2. I don't want to have to write multiple queries. I want one query (or a function) that can take the Column 1 and Column 2 values for each row and get me the Result value for that particular row. I hope this clarifies the question. Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying a database for values in each row
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. -- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying a database for values in each row
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. -- |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying a database for values in each row
Thanks for all the ideas. I ended up solving this with a simpler, inelegant solution. I created a worksheet with a query which returns a copy of the table from the Oracle database. I then added a lookup function to the original worksheet to match the values returned from Oracle. In order to do the match this way I had to concatenate several columns that would normally have appeared in a WHERE clause. That allowed me to use Excel's lookup functions which match a single value in a table against another single value. I will still investigate your solutions because they are more useful for future use. Thanks again for all the ideas. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying a database for values in each row
Dick,
You cannot query a recordset as if it were a table, so no, you have to do the join in either Oracle or Excel (or use ODBC pass through e.g. linked tables in MS Access). And to get the Oracel table into Excel you should be able to use Jet's SELECT..INTO syntax to import the data direct (not using a recorset = faster) but I haven't tested this for Oracle. -- "Dick Kusleika" wrote in message ... 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. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querying a database for values in each row
ODW
Too bad. Thanks for the info. Another scenario that I want to test is bringing the recordset over one time at Workbook_Open, then just looping through it as the function is called. That way the function isn't creating network traffic. I'll report back if I ever get around to doing any of this testing. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "onedaywhen" wrote in message om... Dick, You cannot query a recordset as if it were a table, so no, you have to do the join in either Oracle or Excel (or use ODBC pass through e.g. linked tables in MS Access). And to get the Oracel table into Excel you should be able to use Jet's SELECT..INTO syntax to import the data direct (not using a recorset = faster) but I haven't tested this for Oracle. -- "Dick Kusleika" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Querying Large Database | Excel Discussion (Misc queries) | |||
Summing values in a database against certain criteria | Excel Worksheet Functions | |||
SUMIF positive values only from database | Excel Worksheet Functions | |||
How to aggregate values in a database | Excel Worksheet Functions | |||
Querying Access Database | Excel Worksheet Functions |