Solved

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

Posted on 2004-04-04
5
291 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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.

758 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

22 Experts available now in Live!

Get 1:1 Help Now