Pass Through Queries

[Max Amsterdam]

I have a need to use a pass through query where I need to
do a select statement using an ODBC datasource for a table
on a DB2. and exists statement to a access table on my
local pc.

If both the tables are on the Server AS400 DB2 database I
can use a select statement to do a join using the same
Client Access ODBC Driver Datasource. e.g

select IBITM from F4102
and exists
(
select ITM from ITEMtable
where IBITM = ITM
)
Now?
How can you do the same query if the ITEMtable exists on
my local Access DB and the F4102 is on the AS400 database??
please help
regards
Max

 

[Tom Ellison]

Dear Max:

Why do you need it to be a pass-through? Couldn't you run it in
Access with the table on the AS400 as a linked table?

If you do it this way, it should work well. An inner join between the
tables might be faster.

SELECT IBITM FROM F4102
INNER JOIN ITEMTable
ON ITEMTable.ITM = F4102.IBITM

You could also try:

SELECT IBITM FROM F4102
WHERE IBITM IN (SELECT ITM FROM ITEMtable)

You might try all 3 to see which is faster.

The alternative is to create the ability for the AS400 to access your
local table, if this is possible. Otherwise, a pass through isn't
going to work. A pass-through means the AS400 is going to do the
work, and if it cannot see ITEMtable then it just isn't going to
happen.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts