Join Access and DB2

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to wirte a query in Access that joins a DB2 table to an Access
table. Whenever I try to use a JOIN statement, I cannot get Access to
recognize both environments. It either thinks both tables are in Access or
that both tables are in DB2. A simpler query retrieves data fine from one
table or the other. It appears that joining them requires something fancy.
Any advice?

Thanks,
George
 
George said:
I would like to wirte a query in Access that joins a DB2 table to an Access
table. Whenever I try to use a JOIN statement, I cannot get Access to
recognize both environments. It either thinks both tables are in Access or
that both tables are in DB2. A simpler query retrieves data fine from one
table or the other. It appears that joining them requires something fancy.
Click to expand...

Is the DB2 table linked to the Access file (.mdb)?
 
No, the DB2 table is not linked to the Acces file. I am writing a
pass-through query that is trying to join a local Access table with a DB2
table.
 
George said:
No, the DB2 table is not linked to the Acces file. I am writing a
pass-through query that is trying to join a local Access table with a DB2
table.
Click to expand...

Can't be done. A Pass-Through is the literal sending of a statement to the
server where it is executed. It has to be constructed such that it would run if
you entered it directly into a console attached to the server. The server has
no knowledge of your local Access table at all and will look for that table *on
the server*.
 
Thanks Rick,
I was afraid of that, but I thought it was worth asking, in case someone had
some magical solution.
 
Any reason you don't want to create a link?

George said:
Thanks Rick,
I was afraid of that, but I thought it was worth asking, in case someone had
some magical solution.
Click to expand...
 
Well, I'm not sure how to link to a DB2 table. I have set up linked tables
to other Access databases before. Is it similar? Am I linking to the DB2
table or a pass-through query? I assume that the processing would be done in
Access on my local PC (which, because of the size of the data - 11 million
records - is slow).

Are there any advantages in linking, or do the disadvantages outweigh them?

Thanks,
George
 
George said:
Well, I'm not sure how to link to a DB2 table. I have set up linked tables
to other Access databases before. Is it similar?
Click to expand...

If you are using an ODBC driver for DB2 to create your Pass-Through then you can
use that same driver to create a link. When you go through the link wizard ODBC
sources will be in the list of types.
Am I linking to the DB2 table or a pass-through query?
Click to expand...

You can link to a table or view or you can create a pass-through and then use
any of those in another Access query that joins them to your local table.
I assume that the processing would be done in
Access on my local PC (which, because of the size of the data - 11 million
records - is slow).
Click to expand...

Well it is true that a query that joins a local table to a linked table will not
be efiicient, but it is not a given that ALL processing will be done locally. A
lot of data might have to be sent one way or the other to accomplish the join
though. How large is the local table?
Are there any advantages in linking, or do the disadvantages outweigh them?
Click to expand...

Depends on what you need to do. While there can be performance issues there are
some things you can only do with a link (bind them to a form and do edits for
one).

Is the join to the local table just needed to limit rows returned?
 
Rick,
Thanks for the insights. The local tables have less than 500 records, and
are only used to limit the resulting recordset. (For example, the DB2 table
has a "state" field, while one local table maps "state" to "territory". One
desired query would return dollars for a given territory.) It looks like it
may be better to build all of the tables in DB2 and use Access to run a
pass-through query which joins the two DB2 tables and returns the filtered
records.

There are no forms and no reason to do edits on the data. Is there any
other reason to continue pursuing the idea of having tables in two places
(DB2 and Access)?

Thanks,
George
 
Back
Top