Append new data from different database

[Kamil]

Hi.
I need to periodically append new data from another database (located
in different country).
Now I'm using ADO (with DSN) to download whole tables (INSERT INTO xxx
SELECT ...).

My question is: how to select only specific records on the remote side
to avoid downloading big tables?
My tables have complex, multi-field keys. So I was thinking:
SELECT * FROM RemoteTable WHERE RemoteTable.A<>LocalTable.A AND
RemoteTable.B<>LocalTable.B

It won't work because my query is executed in ADODB connection (so on
the other side).

How can I automatically synchronize my base with remote one?

I'm using Access 2007, remote db is Oracle.

Thanks for help.
Kamil

 

[John W. Vinson]

Hi.
I need to periodically append new data from another database (located
in different country).
Now I'm using ADO (with DSN) to download whole tables (INSERT INTO xxx
SELECT ...).

My question is: how to select only specific records on the remote side
to avoid downloading big tables?
My tables have complex, multi-field keys. So I was thinking:
SELECT * FROM RemoteTable WHERE RemoteTable.A<>LocalTable.A AND
RemoteTable.B<>LocalTable.B

It won't work because my query is executed in ADODB connection (so on
the other side).

How can I automatically synchronize my base with remote one?

I'm using Access 2007, remote db is Oracle.

Thanks for help.
Kamil

I've been thinking about this for a day or so and I can't think of any *good*
way that doesn't require sending a lot of information up or down the wire. A
Passthrough query would at least put the load on the remote Oracle database
(which I expect can handle it easily, though the DBA might not appreciate the
suggestion!), but if you need to upload *your* entire table every time to do
the comparison, you're not ahead. My only suggestion would be to at least try
doing the query in a "frustrated outer join" rather than the not-equals WHERE,
since the latter will probably force a full table scan:

SELECT RemoteTable.*
FROM RemoteTable
LEFT JOIN LocalTable
ON RemoteTable.A = LocalTable.A
AND RemoteTable.B = LocalTable.B
WHERE LocalTable.A IS NULL;

This will find all records in RemoteTable which do not exist in LocalTable
(based on the keys), but of course will not find records which exist in both
tables but have been changed. The query optimizer might be able to come up
with a better plan, but if LocalTable is very large it will still require a
lot of data be moved.