We have a replicated Database running in different countries (MS SQL Server 2000 does a merge replication from Germany to Poland every full hour). There are some tables just counting
an ID field one number up using this field as a key.
This means when two users in different countries create a new record, BOTH get the same key. We tried to solve the problem using an insert trigger updating a 'country' field, working fine on SQL-Server, but Access crashes shortly after inserting the record. The record is saved correctly.
There is no error message in Access, the whole application crashes with "the process 'read' can't be progressed" (or something, using german Acces
We tried different solutions, like using a default value read from a not-replicated table, but this also crashes Access in the same way.
Using a default value on SQL-Server not read from a table, everything worked fine.
Our suggestion was that Access don't like changing primary keys while inserting (as far as the changes are made by triggers or stored procedures reading tables...??), but how can Access be persuaded to do this ?
Is there any suggestion how this could be solved ?
I even programmed a trigger checking the other database in the other country before inserting the value (counting one up if it is not yet replicated but exists in the table),
but .... changing the key, Access crashes.
Thanks in advance and
greetings from Germany,
Jens