Solved

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

Posted on 2004-04-04
5
293 Views
Last Modified: 2008-02-07
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
Comment
Question by:northwindccohio
  • 3
5 Comments
 
LVL 17

Assisted Solution

by:Karen Falandays
Karen Falandays earned 125 total points
ID: 10754089
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
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 125 total points
ID: 10754464
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10754473
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
 

Author Comment

by:northwindccohio
ID: 10756591
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10761344
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Looking for a program that deletes all files of same size 18 59
VBA Access 2016 syntax 6 38
Top 1 of each supplier 55 54
Access query expression 6 19
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now