Solved

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

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

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

730 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