Results 1 to 2 of 2
2001-07-31, 15:15 #1
- Join Date
- Jan 2001
- Serbia and Montenegro (Yugoslavia)
- Thanked 0 Times in 0 Posts
Pass-Thru Queries to Oracle (Access 97, Oracle 7.3)
At my work I use MS Access 97 to create queries to run reports against our Oracle 7.3 server. The reason I do this is because the queries get very complex and at a certain point I need to visualize the tables and joins (and join types). I just started using pass-through queries in an attempt to let Oracle do the heavy work on the back end. I've done this by building the query on the query grid, then opening up a blank query, setting it to pass-through and writing the raw SQL.
One thing I can't do is reference a local table in the pass through. For example, I was given an Access table with several hundred account numbers in it, and had to show all accounts that did not equal this list. I brought the Pass-through query into a query grid (which takes forever to load), then did an "In (Select . . .)" on the local table.
I don't have write access to the Oracle server.
My question is first, is this the best way to accomplish this? Second, what exactly is happening behind the scenes: does Oracle run the pass-through first, return that recordset locally then run it against the sub-query that references the local table?
I appreciate any input.
2001-08-01, 20:28 #2dave_hillGuest
Re: Pass-Thru Queries to Oracle (Access 97, Oracle 7.3)
In order to beat pass-through queries into submission (sorry for the violent reference) you need to manipulate its SQL property in code. To solve your example, you might try:
1. Creating a recordset of accounts
2. Making an appropriately delimited string of values from the account numbers for Oracle (i.e., 'A9987XQ', 'FR7744', ...) by looping through the recordset and concatenating the right characters
3. Inserting the string of values into the pass-through's SQL property (i.e., "WHERE ACCT_NUM In( " & strValues & ")"
It's takes some playing with, but the performance is worth it.