Table primary key has gone from 11982 to 773324886 ????

[atrium]

This table has many relationships based on it's primary key. I now get overflow problems in some areas.

How can I fix the table ??

How can I stop it happening again ??

I had some weird things happen yesterday in random areas. I compacted and repaired the database and everything appeared OK. This could have happened then - not sure

Any help would be soooooo helpfull

 

[theDBguy]

Are we talking about an Autonumber field? If so, take a look at this.

Microsoft Access tips: Fixing AutoNumbers when Access assigns negatives or duplicates (allenbrowne.com)

 

[CJ_London]

what is the datatype of the primary key? if autonumber is it set to incremental or random?

Beaten by DBG

Maximum value for a long is 2,147,483,647.

​

 

[atrium]

Yes it is a autonumber Primary key and is set to incremental.

I have already completed a Compact and Repair

 

[The_Doc_Man]

As far as this number is concerned, it is within range of a LONG, since it is only a 9-digit number, and LONG can hold 4,000,000,000.

This table has many relationships based on it's primary key. I now get overflow problems in some areas.

If so, you have misconstructed your tables because for relationships to work properly, they would have to have the same data types on both ends of the relationships. Rather obviously, you have some INTEGER (WORD integer, 16-bit variety) as foreign keys to the table where this key is PK. Therefore, you have two problems. (Maybe three.)

1. Find out why it skipped so many numbers.
2. Enlarge all the fields that are shorter than they should be.
3. Recognize that if the value in the PK actually matters to you AND is an Autonumber, then you have done something intrinsically wrong - because Autonumber PK values CANNOT matter. They are not suitable for auditing purposes to establish continuity because they are not guaranteed to be contiguously numbered. (This one doesn't apply if the PK is NOT an Autonumber.)

EDIT: Your post crossed mine in transit, so #3 also applies.

 

[CJ_London]

Have you imported any data recently which could have accidentally populated the autonumber field - although you cannot manually enter or change a value in this field you can enter a value when the field is populated via a query or perhaps something other method such as transfertext/spreadsheet.

 

[atrium]

Can I create a query to change the 2 Primary keys to what they should be. Will this fix it so that the next record added will follow on from the new numbers

 

[arnelgp]

if your db is offline, you can "trim" those numbers and still be used by other
"dependent" tables.
1.backup your db.
2. you need to identify all tables that have "fk"s to this table (write them down
and the fk field names).
3.create new field to this table (numeric, long, name it "old_pk").
4.copy the pk over this new field (update query).
5.create new table from the table (copy/paste, structure only).
6.insert records from old table to new table (do not include the
pk from the old table).
7.(new table now has new, sequential, autonumber).
8.update all "dependent" table from the new table's pk:

update tbl1, [new table] set tbl1.fkField=[new table].pkField where
tbl1.fkfield = [new table].old_pk;

9.if all is well delete the old table and rename the new table.

 

[CJ_London]

No - as already stated they cannot be changed. If it matters to you, delete the records, including any related records, compact/repair which should reset the autonumber and then reinsert the deleted records.

Edit - or follow Arnel's suggestion

However as Doc has advised you, check all FK's to make sure they are longs and not integers.

As to how to stop it happening again, it is anyone's guess without more information. Perhaps the db isn't split? Users don't have their own copy of the FE? An import spec is not correct? BE is corrupted? Users are connecting wirelessly?

 

[The_Doc_Man]

As noted by arnelgp in post #8, this is not a trivial fix. However, if you have an autonumber, there is ZERO chance that you won't get more gaps (though usually such gaps are smaller, 1 or 2 counts).

As noted by CJ, continuing to use an Autonumber is going to be unpleasant for you in the long run if the PK has a specific meaning. Autonumbers CANNOT be used for typical auditing requirements because there is ZERO guarantee of contiguous numbering.

What is you REAL requirement for that PK? Not just that it is a PK, but you seem to care about what value it has. So... what is the requirement related to that value?

 

[Pat Hartman]

Can I create a query to change the 2 Primary keys to what they should be.

No.
Create a query that selects just the two rows with a problem and change it to a make table query. Once you are sure the data is copied, you can delete the two rows as long as there are no dependent records in child tables. If there are, the problem gets worse because you have to copy those records to new tables also.

Assuming no child table dependencies.
1. delete the two records.
2. compact the db
3. either type the values from the make table or use an append query to copy them back. In the append query, do NOT select the PK. You want Access to create a new one.

If the new PK is in the range of the rest of the keys, everything is fine. If the new PK is still out of whack, you will need to reset the seed. Let us know where you are with the suggestions and I'll post the seed example if you need it or you can search for it. I've posted it multiple times and others have also.

 

[gemma-the-husky]

You will only get overflow problems if you try to store the 4-byte long in a 2-byte integer (max 65535).
If you are using 2-byte integers elsewhere, than change them to longs, as this will catch you at some point.

You should make sure that matching keys and foreign keys are of the same type. ie both longs.

You just cannot use an autonumber to provide an intact numerical sequence, and you won't be able to set the next number seed to a value smaller than the highest number in the table.

As Pat just noted, delete the records, and then you can reset the seed, but this will be hard if those numbers have already been used elsewhere, and note that this problem may occur again, at any time.

 

[atrium]

Thanks for all your input. This is what I used.
I took a copy of the table in question, deleted the corrupt rows (4 of them) and then Compact and repair. I was told that it would it would tidy tables up and then create the next PK from the last PK value + 1, and it worked perfectly. I had to then manually add the 4 corrupt records back onto the parent table. Then go through all related records and change the MatterId ( a link to the new one created on the parent table) which is a secondary key on the child tables.

To answer the question about why I use the PK. I needed an auto number (that I thought would never be changed by a user or programmer) to be the unique ID for each Parent record created. This is the first time I have had this problem for 7 years of operation.

Thanks again everyone

 

[The_Doc_Man]

I don't want to harp on this, but it bears repeating. The autonumber would, indeed, be a unique number for the record. In fact, the number tht it jumped to would have worked fine. The problem with your situation (as you explained it) isn't uniqueness. It is contiguous (or perhaps you prefer the word continuous) numbering. You CANNOT under ANY CIRCUMSTANCES expect this to happen perfectly. In a shared DB, autonumbers will not be contiguously numbered. If you can live with this, fine. If you require continuous/contiguous numbering, this will not work.

 

[atrium]

Doc Man thanks for your explanation, I respect your views and will take it on board.

Thanks again

 

[gemma-the-husky]

What happens is that the next number seed can get altered - who knows why - and the sequence jumps. Occasionally (maybe this is now fixed) it gets set to a lower value, and then you get data inserts failing because of a duplicate number being issued.

Resetting numbers is painful as you have seen, and in truth you did get a unique number, just not a sequential one.

Note that if you start to edit a record, and then cancel, you can lose one of the autonumbers, and you get gaps in the sequence, which is why an autonumber can't guarantee an intact sequence.

 

[atrium]

Thanks gemma I take you point. Reconsidering my intentions, it's really uniqueness is what I'm after but I didn't expect a nine digit number

 

[CJ_London]

Where my users might have sight of the ID field I tend to use random for autonumbers, which can be positive and negative, to discourage them from assigning any meaning to the value.

They might have sight because of the need to display some record 'parameters' in locked controls in a form footer or there is a requirement to synchronise data

 

[Pat Hartman]

I am disturbed by the fact that you could delete the four records with the large PKs and that didn't prevent the child records from existing. This tells me that you are not enforcing Referential Integrity. This is a very important concept in relational databases. I recommend you fix this before putting this issue to bed. If Access won't allow you to enforce RI, that means that you have more bad data in the child tables and will need to remove it first before RI can be enforced. Don't even worry about the delete. The data is orphaned and is useless without a proper parent record so just delete it and chalk it up to lesson learned.