• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

Replication has caused auto numbers to corrupt upon entry in all tables.

SInce running replication on the database I have run into the problem of all tables having any auto number field become corrupt and even negative numbers upon command from the form entry. I did back up the database before replication , but I am looking for a better solution to the cause then just to start over. Anyone have any ideas?
0
northwindccohio
Asked:
northwindccohio
  • 3
2 Solutions
 
Karen FalandaysTraining SpecialistCommented:
Hi northwindccohio,
With replication, the autonumber field is a no-no unless you can change the field size from long integer to "replication ID". It may tell you that you will lose some data, so do this on a backup copy of your data
Lots more avalable in the Access help under replication
Karen
0
 
Arthur_WoodCommented:
actually, when you declare a Database to be used for REPLICATION, Access changes the 'autonumber' fields from SEQUENTIAL to RANDOM, and thus the Replicated Autonumber fields become RANDOM Long Integers (both positive and negative values) between
–2,147,483,648 and  2,147,483,647.

You should NOT use Primary Keys that are autonumbers for ANY REAL purpose in your design, and should avoid displaying the autonumber values to users.
0
 
Arthur_WoodCommented:
the 'autonumber' fields in your replicated tables have not become 'corrupt' ansd the values that are being generated are 100% correct.  This is by design.

If you need an explanation of WHY the autonumber fields are now RANDOM Long integers, I will be glad to explain, in detail.  Just ask.

AW
0
 
northwindccohioAuthor Commented:
Thanks for the help, it would have been nice if you had just passed on info about random long integers in your explanation. Back up here I come. Thanks again.
0
 
Arthur_WoodCommented:
the Autonumber values are made RANDOM when you REPLICATE the database to avoid primary key conflicts when the Replicas are synchronized - 'brought back together'.

The primary reason for using replication is so that you can have several copies of the REPLICAS, used by independent users.  As these users add records to their separate copies, they generate new records, which need NEW and UNIQUE primary keys.  If the autonumber fields reamined sequential, the when user A added a new record to Table 1, they would get autonumber field + 1 as the primary key, but when User B added a new record to Table 1 (in their replica) they would ALSO get autonumber fields +1 (and that would be a conflict when the two replicas were sunched with the MASTER, as the two replicas would attempt to add the SAME primary key for two independent records in the same table).  By having the autonumber field now be RANDOM, instead of sequential, then User A's new record gets a completely DIFFERENT value for the primary Key than doews User B, an both new records can then be inserted, without error, into the Master table, when the replicas are synchroniozed.

This is all explained in the discussion about Replication in the Access Help system.

AW
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now