Synchronization Question

[Guest]

After reading several other posts on replication and synchronization, I ran a
test with my multiuser database, and everything worked well.

Now I have a couple of questions. First, some explanation. The database
contains data from three separate companies. Currently it is hidden behind
my company's firewall, so inputs from the other companies are sent by email
and we enter them into the database. One of the other companies is in
another state, the other in another country (the U.K.). They both want more
direct access to the database. The database IS split, and we will have to
provide each company a localized version of the front end, but that should be
a no-brainer.

First question: We are using an autonumber field in the main table to track
our items. From my test, I see that this field is changed and the new
numbers look quite random. Is there a way to generate something like our old
autonumbered IDs so that each item has a unique, but sequential number?

Second question: Is there a way we can pre-screen the changes (i.e. do a
difference between the master and the replica) before synchronizing? Or can
we limit the type of changes or the fields that are allowed to change in the
replica? For example, we don't want the other companies to change items that
are flagged to belong to my company (by a field in the main table). Also,
certain fields in ANY record should not be changed by the other companies.

Thanks in advance for your reply!

Eric

 

[Guest]

And finally, one other question. I am assuming that I can create the
replicas for the other two companies locally, then email those replicas to
the other companies, have them make changes and send the files back (once a
week), and then synchronize locally. Is this correct?

Thanks,

Eric

 

[David W. Fenton]

Now I have a couple of questions. First, some explanation. The
database contains data from three separate companies. Currently
it is hidden behind my company's firewall, so inputs from the
other companies are sent by email and we enter them into the
database. One of the other companies is in another state, the
other in another country (the U.K.). They both want more direct
access to the database. The database IS split, and we will have
to provide each company a localized version of the front end, but
that should be a no-brainer.

I would host it with Windows Terminal Server instead of putting it
out on the clients' networks. You could then use partial replicas
for them to edit, or just a single full replica, with 3 different
front ends that filter the data to their own company.

First question: We are using an autonumber field in the main
table to track our items. From my test, I see that this field is
changed and the new numbers look quite random. Is there a way to
generate something like our old autonumbered IDs so that each item
has a unique, but sequential number?

Sequential numbers are very difficult in replicated databases. There
are three options:

1. pre-allocate blocks of numbers to each replica.

2. change the sequence to 2-column storage, with one column
indicating the source replica.

3. don't assign the final sequence values at data entry time, but
have an administrative function that assigns the final value in a
centrally-located administrative replica. The individual sites will
then get the permanent sequence values the next time they synch.

None of these are very good solutions!

Second question: Is there a way we can pre-screen the changes
(i.e. do a difference between the master and the replica) before
synchronizing? Or can we limit the type of changes or the fields
that are allowed to change in the replica? For example, we don't
want the other companies to change items that are flagged to
belong to my company (by a field in the main table).

Why should they see data they can't change? Do they need to see it?

Also,
certain fields in ANY record should not be changed by the other
companies.

Partial replicas should take care of this, or you can engineer a
front end that simple filters out the data for the other companies
(this is what I would do).

 

[David W. Fenton]

And finally, one other question. I am assuming that I can create
the replicas for the other two companies locally, then email those
replicas to the other companies, have them make changes and send
the files back (once a week), and then synchronize locally. Is
this correct?

No, you can't do that. Once a replica has been edited, it has to be
synched IN PLACE. Otherwise, you create dead replicas, which are
VERY BAD:

http://groups.google.com/group/comp.databases.ms-access/msg/9f5a84194
fa6c653

If you use a Terminal Server approach, you wouldn't need replication
at all, in fact.