David W. Fenton replied to lloyd sturge
21-Oct-09 07:58 PM
There are three general solutions to this problem:
1. allocate blocks of IDs for each replica, and select the next
available according to which replica the new record is being added
in. While SQL Server replication has a built-in capability for
implementing this, Jet replication provides no support for it, so
you will have to set it up yourself and implement it in your NEW
RECORD logic.
2. change the PK to be a two-column key, one of which indicates the
source replica, and the other of which is populated according to the
Max()+1 rule. This may or may not work. For instance, if your
code-generated PK is an invoice number, you would have to redefine your
invoice number to include the source replica field in order for this
solution to be usable.
3. use random autonumbers for the real PK and then have one of the
replicas process the data to assign the code-generated numbers that
human beings use. This would not work in some applications, but in
an app where, say, multiple users generate invoices, but only one
person actually prints them and sends them out to customers, it
would work ust fine.
In any case, if you need a meaningful sequence, you have to either
replace your existing PK field with it, or add it as an additional
field to the random Autonumber PK.
I have never had to deal with this, as the only app where I was using
a "custom counter" was one in which it was just simpler to use a
MASTER/SLAVE setup, where only one replica at a time could add
records to the table with the code-generated PK. That app had only
two production replicas, one on a desktop and one on a laptop, and
the synchronization process automatically transfers MASTER status
appropriately (i.e., if the laptop synchs with the desktop, MASTER
status transfers to the laptop; when the laptop next synchs with the
desktop, MASTER status transfers back; there is also an override to
synch without changing MASTER status, but I am the only one who ever
uses it!).
That obviously does not help you in a situation where there is more
than one user.
The simplest solution is to allocate blocks of numbers to each
replica, seems to me. You have a choice of whether to do that by
populating a table and assigning the rangers, or of writing the
blocks into code, or of doing the much easier method, which is to
use the left-most column as the replica-specific number. How that
would work is something like this:
Replica 1 1,000,000 to 1,999,999
Replica 2 2,000,000 to 2,999,999
Replica 3 3,000,000 to 3,999,999
Replica 4 4,000,000 to 4,999,999
To do this, you would assign the right 6 digits and then add N*1,000,000
to it, where N is the number you have allocated to the replica. So, if
the next available number is 9568, your sequence number would be
1009568 if you are generating it in Replica 1, and 2009568 for
Replica 2 and so forth.
This may be problematic for your accountants if they do not want,
say, invoice numbers to have gaps. But you should be able to
maintain each sequence within itself, so that should be an avoidable
(or, at least, explainable) problem. That is, there might be a gap
between 1009568 and 2000000, but there ishould be any gaps within the
sequence between 1,000,000 and 1,999,999.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/