?
Solved

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

Posted on 2004-04-04
5
Medium Priority
?
298 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 18

Assisted Solution

by:Karen Falandays
Karen Falandays earned 375 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 375 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…
Suggested Courses

764 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