synchronizing data

[Guest]

I have a database that gathers data from 15 different agencies. At present,
the data is submitted by each agency in paper form and then entered into the
database. It has been suggested that each agency enter their own data into a
copy of this database and then import this data into the current and original
database.

As I am testing this process, I foresee problems with the primary key in
both the parent and related tables. The primary keys that are currently used
are auto number. How will I allow each agency to create an auto number
primary key and then import these records into the original database?

Also, how can I ensure that only new records (no duplicates) will be
appended into the original db?

I'm sure there are other issues I'm not thinking of in synchronizing the 15
new databases into one. Any help is very much appreciated.

 

[Guest]

I would ask this question in the Replication group. I have not had the
opportunity to use replication, but it looks like a candidate for it. You
are dead on correct about the issues with autonumbering (that can be easily
overcome), but identifying an identical record may be difficult unless you
have some specific rules on what constitiutes a duplicate.

 

[John Nurick]

Hi Deb,

I suggest you add a field to each of the tables concerned to identify
the agency that creates the records (i.e. all records created by Agency
XYZ would have the value XYZ in this field).

In the "master" database, change the current Autonumber field to an
ordinary Number field and remove its unique index, and include both this
and the Agency field in the primary key.

Then when you import data from an agency, (a) the different values of
the Agency field would avoid conflict if the same autonumber has been
assigned to different records by different agencies and (b) the
two-field primary key will prevent the same record being imported twice.

 

[John Nurick]

Like Klatuu I've never used replication. As I understand it, it provides
a transparent and effective way of syncrhonising remote "replicas" of a
database with the "master" at intervals.

But the synchronisation process (if that's the right word) requires a
network connection between the master and the replica. A classic example
is salesmen with laptops: each salesman has a replica, and they
synchronise each time they're in their office.

If that can be achieved between your network and those of the various
agencies, well and good. Otherwise, it would be necessary to shut down
an agency's database, transport the mdb file (email, ftp, CD or
whatever) to you to be updated, and then send the updated file back to
the agency before they could enter any more data. This might be an
unacceptable restriction; and if there's just a one-way transfer of data
from the agencies to the "centre" (i.e. no agency needs to have access
to other agencies' records) it's unnecessary.