Inner Join Query on 2 linked tables

[Binta Patel]

I would like to create an inner join query on 2 tables
that are linked. The tables reside in 2 different
databases. Database1 has table1 that has a key of
XHolder and Database2 has tbale2 that has a key of
XHolder. I only want the records from table2 that match
table1. Table1 has about 5 records and from table 2 it
should return about 700 records. When I run this over
the network the entire process takes about 15 minutes and
would also like to speed it up. Any other suggestions as
to how to do would be appreciated.

 

[Van T. Dinh]

It shouldn't take that long if the databases are stored on the desktop or on
a LAN and you don't include any OLE Fields in your Query.

Post the SQL String of your Query.

 

[Binta Patel]

My clients are using dial-up networking with the speeds
rangin from 14.6 - 28.8 KPB, so it is very slow. The
databases are separated and have a backend(stored on the
network server) and the front end on the local machine.
Table2 which is stored in database2 has about 26000
records, out of which I only need about 700 in my
database1 based on a search criteria from my table1. The
sql string that I am using is:

SELECT * INTO imemo_mstr1
FROM imemo_mstr IN 'G:\Public\Access-Audit\imemos.mdb';

but this string gives all the 26000 records instead of
the 700. What I would like to do is something like this,
but I do not even know if it can be done.

select * into imemo_mstr1 from imemo_mstr
in 'G:\Public\Access-Audit\imemos.mdb' innerjoin
[audit].xhndlr = [imemo_mstr].xhndlr1

The table audit is on my current database that I am using
and table imemo_mstr is located on the G drive on the
network. When I use the above syntax, I get an error
messsage that says table audit is not found. Sorry for
the somewhat lengthy message.

 

[Van T. Dinh]

Forget about using Access over a dial-up networking. It can only create
much frustration! (one of my collegues gave up even on 128 Kbps),

I thought you want a SELECT Query, NOT a Make-Table Query. The String you
posted looks more like a Make-Table Query. Check Access Help for correct
use of these Queries.

HTH
Van T. Dinh
MVP (Access)