Adding only new records from another database Nir N 19.08.2006 17:21:01 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!

RE: Adding only new records from another database Claes D 19.08.2006 19:45:32 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: [Quoted Text] > 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!

RE: Adding only new records from another database Nir N 20.08.2006 04:34:02 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: [Quoted Text] > 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!

Re: Adding only new records from another database "Brendan Reynolds" <brenreyn[ at ]discussions.microsoft.com> 20.08.2006 11:32:12 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" <NirN[ at ]discussions.microsoft.com> wrote in message news:29D38CB2-C5F6-45E6-9DDB-A81D0C9F5E42[ at ]microsoft.com... [Quoted Text] > 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!

RE: Adding only new records from another database Ken Sheridan 20.08.2006 15:50:02 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: [Quoted Text] > 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!