Local Access table in Oracle query

[RLN]

Re: Access 2003/Oracle 9i

I have an Access app that connects to an Oracle DB via OLEDB/VBA code
(no DSN or ODBC)

Queries against straight Oracle tables run fine.

For this query, however, the condition for finding DeptID's in the
Oracle table have to be filtered by a list of DeptID's found in
"tblDeptID" which is a simple local Access table containing one column
called "DeptID". There are 383 rows in this local table.

This code runs except for when I try and reference my local table.

Here is the code:
<begin code>
dim APVdb as Database
Set APVdb as CurrentDB()
strSQL = "SELECT A.MATCH_RULE_ID , " & _
"Count(A.MATCH_ RULE_ID) As CountofMATCH_RU LE_ID " & _
"FROM " &_
"PS_VCHR_MTCH_E XCPT A, " & _
"PS_VOUCHER_LIN E B " & _
"WHERE " & _
"A.BUSINESS_UNI T = B.BUSINESS_UNIT " & _
"AND A.VOUCHER_ID = B.VOUCHER_ID " & _
"AND A.VOUCHER_LINE_ NUM = B.VOUCHER_LINE_ NUM " & _
"AND A.PO_ID IN "
strSQL = strSQL & "(SELECT DISTINCT A.PO_ID " & _
"FROM PS_PO_LINE_DIST RIB A " & _
"WHERE A.DEPTID IN " & _
"(SELECT DEPTID from " & APVdb.tblDeptID & ")) " & _
"GROUP BY A.MATCH_RULE_ID "
<end code

Notice the next to the last line references my local table "tblDeptID" .
I have tried
- Currentdb.tblDe ptID
- dbo.tblDeptID
- Me.CurrentDB(Tb lDeptID)
- tblDeptID
...all to no avail.

I tried a pass-through query, but that does not work.

If you have an example where you are using a local lookup table of
values as a conditional select against an Oracle database, I would be
interested to see how it is done.

Thanks.
*** Sent via Developersdex http://www.developersdex.com ***

[victorcamp]

If the Oracle table is linked, so it appears on the Tables tab along with
your local table, you should be able to use the query design view to link
them appropriately in a saved query. Try it with a saved query first, to at
least get it to work with limited criteria. Then use the Access-generated
SQL to assure your code is properly worded.

If need be, use the saved query every time, but change the criteria via code
as needed.

[RLN]

Victor,
Thanks for writing back.
When you have Oracle SQL and need to use data in a local Access table as
criteria for your Oracle extract, here is the solution via an ADO
recordset.

Just for reference, here was my dilemma:
All of the tables in my referenced query ('PO_ID', 'PO_VCHR',
'PS_VOUCHER_LIN E' et al) are Oracle tables. The only table that is a
local Access table is "tblDeptID' s") My application links to Oracle via
OLEDB using VBA code. There is no ODBC data source and no DSN.
To extract Oracle data I needed to use 300+ DeptIDs from a local as
criteria for the Oracle data extract. I collaborated talked with a
co-worker and he actually came up with a cool workaround. Nice job,
buddy!

The workaround consists of building the sub-SQL string that contains the
DeptID's, then incorporate that into the bigger Oracle string. Here is
the code to do it:

<begin code>
Dim rsDeptID As ADODB.Recordset
Dim strDeptIDSQL As String
Dim strDeptIDs As String

Set rsDeptID = New ADODB.Recordset
rsDeptID.Active Connection = CurrentProject. Connection
strDeptIDSQL = "Select * from tblDeptID order by DeptID"
rsDeptID.Open strDeptIDSQL 'we're using the currentproject connection
here as set above

Do While Not rsDeptID.EOF
'build your DeptID string here
strDeptIDs = strDeptIDs & " '" & rsDeptID.Fields (0).Value & "' ,"
rsDeptID.MoveNe xt
Loop

'trim off the last comma
strDeptIDs = Left(strDeptIDs , Len(strDeptIDs) - 1)

rsDeptID.Close
Set rsDeptID = Nothing
'you now have a string you can use in your Oracle SQL.

strSQL ="SELECT A.MATCH_RULE_ID , " & _
"Count(A.MATCH_ RULE_ID) As CountofMATCH_RU LE_ID " & _
"FROM " & _
"PS_VCHR_MTCH_E XCPT A, " & _
"PS_VOUCHER_LIN E B " & _
"WHERE " & _
"A.BUSINESS_UNI T = B.BUSINESS_UNIT " & _
"AND A.VOUCHER_ID = B.VOUCHER_ID " & _
"AND A.VOUCHER_LINE_ NUM = B.VOUCHER_LINE_ NUM " & _
"AND A.PO_ID IN "
strSQL = strSQL & "(SELECT DISTINCT A.PO_ID " & _
"FROM PS_PO_LINE_DIST RIB A " & _
"WHERE A.DEPTID IN " & _
"(" & strDeptIDs & ")) " & _
"GROUP BY A.MATCH_RULE_ID "
strSQLTestScrip t = strSQL
<end code>

Here, 'strDeptIDs' string (3rd line from the bottom in the above sql
string) would contain the DeptIDs retrieved above when the SQL string
was created via the ADO recordset going against the 300+ rows in local
table 'tblDeptID'. The ADO recordset runs in a tenth of a second
(tracked with a timer earlier) so there's no overhead to doing it this
way.

*** Sent via Developersdex http://www.developersdex.com ***

[Tim Marshall]

I don't use OLEDB as it does not allow one to make use of Access reports
and so have stuck with DAO and ODBC.

I'm pretty sure you simply can't combine operations with a local table
with your Oracle db. Remember that a local table is not Oracle, but is
an MS Jet object.

I would do what I think you've described in your follow up message:
create a string from an MS Jet query and then plop it into the where
clause of an Oracle statement.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me