Custom Autonumber problem in Replicated App

[Penny]

Hi all,

I built a multi user access database(split with MDE FE's on clients - BE on
office server) a year ago with little prior experience and implemented a
custom self incrementing id number (CandidateID) as a friend suggested. The
Candidate ID needs to be in a consistent format as our regulatory laws
require it so. We started at Candidate ID 450 and are now at around 600.
The Candidate ID is also the primary key for the table. All this has worked
beautifully until now as I have just replicated the database(BE only) to
allow for synchronization between server and laptops when a staff member has
been working away.

To allow the users to work on the database 'at home' they have an extra
version of the FE MDE on the laptop connected to a replicated version of
the BE in a 'windows briefcase' also on their laptops. They use this second
FE/BE pair only when off the network. They sychronize the 'offline BE' and
server BE as they leave the office and again when returning to the office
if they made any changes while away.

Thing is, if they've added a new Candidate while away there may be a new
Candidate added to the Server BE in the meantime and they will often have
the same Candidate ID as each version of the database has added 1 to the
last Candidate ID number which on both of them may have been 589 at the
time. Hope this is clear. Must be a common problem and maybe a large design
oversight on my part.

There is coverage of using a 'custom autonumber routine' in the A2K2
Enterprise Dev Handbook but it doesn't seem to address

my particular issue.

Any ideas on how to resolve this issue?

Regards

Penny.

 

[Douglas J. Steele]

Replicated databases have random Autonumbers, not sequential ones. There is
no way to change this (and unreplicating the database will not convert it
back). Are you saying that your Autonumber field didn't get modified?

Autonumbers should never be used if their value is important. They exist for
one purpose only: to provide a (practically guaranteed) unique value that
can be used as a primary key. It's rare that the users even see the value of
an autonumber field.

 

[Penny]

Whoops. I apologize,

The Candidate ID is not an actual 'autonumber' datatype. Its an integer
incremented manually by my custom code. There is no autonumber datatype used
in this table.

The issues I mentioned revolve around the fact that if a new CandidateID of
480 is generated on the office BE and the same Candidate ID number is
generated on a laptop using the replicated BE. When they are synchronized
together I'm stuck with two distinct Candidates with an ID of 480.

Sorry 'bout that.

Penny

 

[Douglas J. Steele]

You'll either have to use an Autonumber (so that Access can take care of it
for you), or else do something like have different ranges of CandidateID for
the two environments.

 

[Luke Dalessandro]

Another option might be a manual insert - delete of newly generated
offline records before a synchronization...

Presumably, new records entered offline don't exist in the online
backend, you could run a custom insert on all new records from the
offline database, and a delete to get rid of them from the offline db.

Then you can synchronize to get any edits made to old records.

If you timestamp record creation you can do a simple outer join from the
offline db to the online one to determine new records for insertion and
subsequent deletion...

Luke

 

[Penny]

Luke, are you saying that the new Offline BE records should be added to the
Main BE table, the incremented ID numbers generated and applied, the records
removed from the Offline BE and then allow the synchronization process to
re-add them from the Main BE to the Offline BE (as if they'd never been
there at all and with the correctly assigned Candidate ID)?

Regards

Penny

 

[Penny]

You'll either have to use an Autonumber (so that Access can take care of it
for you), or else do something like have different ranges of CandidateID for
the two environments.

I guess we could use an Autonumber but seeing as it is replicated all
Autonumbers become random long integers which are really long, incontiguous
and aren't suitable in a business sense.

There are 4 laptops and therefore 5 'environments' so I can see how that
would make sense.

Thanks for your input though Douglas and Luke

 

[Joan Wild]

Luke, are you saying that the new Offline BE records should be added to
the
Main BE table, the incremented ID numbers generated and applied, the
records
removed from the Offline BE and then allow the synchronization process to
re-add them from the Main BE to the Offline BE (as if they'd never been
there at all and with the correctly assigned Candidate ID)?

That is not what would happen. During synchronization, deletes are done
first. So adding them to the Main, and then deleting them in the Offline
BE...when you synchronize, the first thing that will happen is a delete of
these records in the Main.

It seems to me that your custom # should be unique. Then you can just deal
with the conflict when it comes up.

 

[Penny]

Hi Joan,

Yes the custom # must be unique ( and contiguous). The problem is how can we
resolve the fact that the same custom number may have been applied in the
two seperate environments(the office network FE/BE environment and the
laptop FE/BE environment?

Then of course there is the situation where two of the laptops may have
generated the same number independently, then synchronized with the main
office BE.

I'm beginning to think a custom incremented number (with a need to be
contiguous and unique) is too difficult to implement in a replicated
environment.

Thanks for your response Joan

Regards

Penny

 

[Joan Wild]

Yes the custom # must be unique ( and contiguous). The problem is how can
we
resolve the fact that the same custom number may have been applied in the
two seperate environments(the office network FE/BE environment and the
laptop FE/BE environment?

In any replicated database there are always conflicts to be resolved. This
is just one of them. The sync will report the conflict; a human has to
resolve it. There is no automatic way around this. Any replicated database
will have to deal with conflicts.