Adding only new records from another database

I have created a customers table that feeds from another Acesss database's
customer list table.
The other database is constantly being updated -new records (customers) are
added daily.
I need to periodically scan this "foreign" database and add only new
records to my own database (to keep the two tables synchronized in some
fields only).
How can it be done?

Thanks!

How do you transfer the records?
Code or manually?
Im a newbie too, but I would create a new field in the table (in the
"foreign" database) that shows if the records have been transferred.
Then create an update query that sets todays date in the new field. (if the
value is Null.)
The query should run after transfer.

This should work regardless of how you transfer the records.

/Claes


"Nir N" skrev:

I have created a customers table that feeds from another Acesss database's
customer list table.
The other database is constantly being updated -new records (customers) are
added daily.
I need to periodically scan this "foreign" database and add only new
records to my own database (to keep the two tables synchronized in some
fields only).
How can it be done?

Thanks!

Thanks for your suggestion.
I can use either an update query or code. However I cannot modify the
"foreign" database as it is a 3rd party, read-only Access program. Therefore
I need to check for duplicate records at the level of the "receiving" table
/ form.

Nir

"Claes D" wrote:

How do you transfer the records?
Code or manually?
Im a newbie too, but I would create a new field in the table (in the
"foreign" database) that shows if the records have been transferred.
Then create an update query that sets todays date in the new field. (if the
value is Null.)
The query should run after transfer.

This should work regardless of how you transfer the records.

/Claes


"Nir N" skrev:

I have created a customers table that feeds from another Acesss database's
customer list table.
The other database is constantly being updated -new records (customers) are
added daily.
I need to periodically scan this "foreign" database and add only new
records to my own database (to keep the two tables synchronized in some
fields only).
How can it be done?

Thanks!

Here's an example I posted recently in answer to a similar question ...

INSERT INTO TargetTable (TargetID, TargetText) SELECT SourceID, SourceText
FROM SourceTable WHERE SourceID NOT IN (SELECT TargetID FROM TargetTable)

Here 'SourceID' is the primary key of the table you're copying from, and
TargetID is the primary key of the table you're copying to.

--
Brendan Reynolds
Access MVP

"Nir N" wrote in message
...
Thanks for your suggestion.
I can use either an update query or code. However I cannot modify the
"foreign" database as it is a 3rd party, read-only Access program.
Therefore
I need to check for duplicate records at the level of the "receiving"
table
/ form.

Nir

"Claes D" wrote:

How do you transfer the records?
Code or manually?
Im a newbie too, but I would create a new field in the table (in the
"foreign" database) that shows if the records have been transferred.
Then create an update query that sets todays date in the new field. (if
the
value is Null.)
The query should run after transfer.

This should work regardless of how you transfer the records.

/Claes


"Nir N" skrev:

I have created a customers table that feeds from another Acesss
database's
customer list table.
The other database is constantly being updated -new records (customers)
are
added daily.
I need to periodically scan this "foreign" database and add only new
records to my own database (to keep the two tables synchronized in some
fields only).
How can it be done?

Thanks!

Provided both the source and target tables have a primary key, e.g.
CustomerID you can simply attempt to insert all rows from the source table.
Those already represented in the target table will be rejected by virtue of
the key violations, and only the new rows inserted.

The mechanics for importing the data are simply to create a link to the
source table in the target table, and base an 'append' query on this linked
table, setting it up to append to the target table. If you run the append
query via the user interface you will get a message telling you that n number
of rows were not appended due to key violations.

Ken Sheridan
Stafford, England

"Nir N" wrote:

I have created a customers table that feeds from another Acesss database's
customer list table.
The other database is constantly being updated -new records (customers) are
added daily.
I need to periodically scan this "foreign" database and add only new
records to my own database (to keep the two tables synchronized in some
fields only).
How can it be done?

Thanks!