autonumber & replication

[Dillen]

We have a database to track errors during testing.
The database has MS Access front-end and SQL server back-end.

The project team is located in Europe and US.
To increase access performance for the US team we want to replicate the back-end database to an US server.

The main table containing the test errors uses an autonumber field as table key.
If a new entry is created in US and Europe, the same key might be used.
What's the best solution of have different number ranges in US and Europe, but still using autonumber fields.

Thanks in advance
Christof.

 

[Dillen]

just an additional information: we have also 2 front-ends on different servers in US and Europe.

Christof.

 

[Pat Hartman]

Are you actually going to try to use replication? I don't know if it works with non-Jet back ends. You probably need to look into what is available with SQL server.

If it turns out that SQL server does not support replication, you can do the following.
1. Replace the autonumber pk with a long integer and add your own code to generate the next sequential number. Use DMax().
Me.SeqNum = Nz(DMax("SeqNum", "YourTable", "ReplicaNum = " & Me.ReplicaNum), 0) + 1
Don't forget that in a multi-user environment this code could generate duplicates so you need to trap the duplicate key error and generate a different sequence number.
2. Add a new column to each table to identify the copy.
3. Make the two fields the compound primary key. This will allow you to merge the two sets of data without any conflict. The number series will be duplicated but the second key field will keep them separate.