Export, then Import. Duplicate key problems

[Randy]

Access 2000. I have an employee database that will be installed in 10
different offices throughout the state. In my db I have a main table which
has employee name, number of hours worked, miles driven during the day and
expenses. I also have a details with referential integrity to my main table
table listing customers visited during the day and a breakdown of monies
collected, hours worked, and miles driven to each customer. The intention
is to have each office email an excell file of the two tables to the main
office for import. How do I resolve duplication of the primary keys at the
main database at our office. I can see the db in the field offices could
have the same primary key linking the main table to the details table. When
they are imported problems will arise with duplication of the primary key..
Oh, a web based server is not a possibility...Thanks...Randy

 

[Jeff Boyce]

Randy

Have you looked into using replication?

 

[Guest]

Aside from replication, the other option would be to add an officeID to the
tables and use the combination of officeID and whatever your autonumber
fields are as the primary key for each of these tables.

You might also want to reconsider using autonumber fields in favor of a
function that returns the next unused value for the fields that are currently
autonumber fields. If you don't do this, then in the master database at the
headquarters, you will need to change this field from autonumber to long
integer.

Dale

 

[Randy]

Interesting ideas. I thought you can have only one field as the PK. You
are suggesting using two fields as the PK? I already have an OfficeID field
in the table plus an autonumber field as the PK. How would I go about
combining these two fields as the PK?

 

[Jeff Boyce]

Randy

Open a table in design mode (only "local" tables, not linked).

Highlight the fields that (collectively) represent your primary key. Click
the primary key button. If you open the Indexes window, you should see the
(multiple) fields listed under Primary Key.