Prevent duplicates on import

[Liz James]

Thanks in advance for your help.

Main database on new computer using A2K3 runtime.
Data collection using 6yr old Laptop with A97 from Secondary office 20 km
from main office.

The old laptop has a cutdown version of the database for data entry only (to
save hard disk space on the older laptop). The field data will be imported
into the main database when convenient for user to call at main office.

Data is collected in the field for 2 or 3 months of the year only.

What errors can you see with the following plan for preventing duplicates in
the database when data is updated to the main database in the main office?

A complete set of data to be retained on the laptop.
When import data to the main database, delete the tables involved from the
main database and replace them with the data tables from the laptop.

 

[Douglas J. Steele]

It would seem to prevent you from having relationships set up, at least not
between the set of tables that are going to be deleted and the set of tables
that aren't.

 

[Liz James]

Hi, Doug
Yes, it seems now after your reply, that it is a bit of a rash approach. So
I should import the 2 tables involved and run append queries. The main table
has a unique index involving 5 fields. I am assuming that this unique index
would prevent the appending of duplicate records. Is this assumption correct?
How would I prevent showing an error message after the append if there were
duplicate records and access will say that not all records appended
successfully?

:

It would seem to prevent you from having relationships set up, at least not
between the set of tables that are going to be deleted and the set of tables
that aren't.

 

[Douglas J. Steele]

You might find my Access Answers column in the November, 2003 issue of Smart
Access to be of use to you: it shows how to run a query that can do updates
and inserts in one step. You can download it (for free), along with an
accompanying database, at
http://www.accessmvp.com/djsteele/SmartAccess.html

 

[Liz James]

Many thanks for your suggestion and the time taken to answer my questions.