Problem with keys (1 Viewer)

[doronbs]

Hello,
I recently started working on a database that i believe no one has touched for several years.
For some reason all the keys were completely messed up (even though they were all auto numbers)
And I get really wierd numbers (attached in the picture)
I checked with the person who built it and for sure the numbers were used to be arranged in ascending order from 1. It is not clear how this happened...
Anyone have an idea how can i fix this without starting all over again?

Thanks,
Highly appreciate any help

 

[Gasman]

Looks like to be set to random and not incremental?

 

[theDBguy]

Hi. Welcome to AWF!

If the database still functions, perhaps there's nothing to fix. If the key values are used in related tables and you decide to "fix" them, you might actually break the database.

 

[Uncle Gizmo]

and you decide to "fix" them, you might actually break the database.

Before you do anything, make a backup copy!!! Generally I make a copy AND a Master Copy... MyDb_MASTER... I put it in an archive folder where there's very little chance of me accessing it accidentally

 

[GPGeorge]

You just can't have too many backups.

That is most likely because of using "Random" instead of "Increment" for new values. And, if that's the case, don't mess with it now. Just verify that the related Foreign Key values in other tables match those in this table. I.e. that queries return the values expected when linked on this Primary Key field and the Foreign Key field in the related tables.

 

[doronbs]

Looks like to be set to random and not incremental?

if I set it now to incremental, will the fields change back?
Or can you tell me where can i change it?

 

[doronbs]

Hi. Welcome to AWF!

If the database still functions, perhaps there's nothing to fix. If the key values are used in related tables and you decide to "fix" them, you might actually break the database.

Well, only if i dont have any other choice... i really wish the values to be clear when i'm searching etc..

 

[Gasman]

TBH I do not know
However as long as the links work, it should not matter, just a matter of cosmetics?
GPGeorge has already shown you where it is set?, probably an errant click in design review of table?
Might want to check all tables.

 

[doronbs]

Hi. Welcome to AWF!

If the database still functions, perhaps there's nothing to fix. If the key values are used in related tables and you decide to "fix" them, you might actually break the database.

Well, only if i dont have any other choice... i really wish the values to be clear when i'm searching etc..

You just can't have too many backups.

That is most likely because of using "Random" instead of "Increment" for new values. And, if that's the case, don't mess with it now. Just verify that the related Foreign Key values in other tables match those in this table. I.e. that queries return the values expected when linked on this Primary Key field and the Foreign Key field in the related tables.

View attachment 97146

So its seems i can't change it anymore... I'll probably leave it that way
Thank you anyway

 

[Gasman]

Well you probably could, but it would be a lot of work, also prone to making a mistake and breaking the relationships.
How many other tables hold the PK from that table as a FK.?

 

[oleronesoftwares]

if I set it now to incremental, will the fields change back?
Or can you tell me where can i change it?

Its advisable you don't , but first confirm the suggestions giving by @Gasman , i.e if set to random as aginst incremental.

There is no rule that says primary keys must increment in a particular order, as long as it serves the purpose of pkeys, then retain it.

 

[GPGeorge]

I am confident that Access will not permit changing from one option to the other, but you could try on a backup copy....

That said, there is no reason for anyone to ever see those values anyway. They are there ONLY for the use of the ACE engine in tracking records by that Primary Key/Foreign Key value. No human needs to see or put any significance on them otherwise.

In my career, I only saw two applications where PKs were exposed to users. In one, they were doing mass mailings and tracked returns by that value, but no one expected it to be anything other than a reliable way to look up a record and differentiate between potentially three or four "John Smiths" who'd received the original junk mail.

In the other, the client had used the AutoNumber as an Invoice Number on their invoices to customers. When we were called in to rescue that application, it was PRECISELY because corruption had caused their AutoNumbers to go out of sync. It cost them several hundred dollars for us and their admin staff to clean up that mess in the relational database application and re-enter the messed up invoices.

Trust me, you do not want users to see, or even know about the existence of, AutoNumbers. Not unless you anticipate additional hourly billings to come your way as a result....

 

[theDBguy]

Well, only if i dont have any other choice... i really wish the values to be clear when i'm searching etc..

It's not very often I would see users perform a search on Autonumber fields. Usually, you would want to search on Text data. For example, in your case, rather than search for a SiteID (-2431546), I imagine it's better/easier to search on SiteName (Company Main Office). Just a thought...

 

[Pat Hartman]

Well, only if i dont have any other choice... i really wish the values to be clear when i'm searching etc..

As the others have said, the autonumber should probably not even be visible. You can go from increment to random but not from random to increment. But, if you want to renumber the PKs, you can, and it isn't actually difficult.

Whenever you are going to do mass updating, you must make a backup and I always zip the backup to prevent accidents.

1. Turn off Name auto correct. It is a dangerous "feature" anyway and in this case, it will interfere. You can turn it back on when you are done if you want to leave it on.
2. Remove all RI relationships
3. Rename the PK to WhateverOLD
4. Save the table
5. Add a new column autonumber column using the old autonumbername.

6. Now, for each and every related table, you need to run an update query. The update query joins the child table to the parent table on FK to WhateverOLD and then updates the FK in the child table to the new autonumber value

7. Then, put ALL the RI relationships back.
8. Remove the WhateverOLD former PK.

If you didn't use RI to begin with, you should have. But now when you try to implement it, you are likely to run into bad data. Clean up the bad data. You might as well just delete the rows since you have lost the original parent record. Once the data is clean, enforcing RI will work.

Doing this renumbering will not put the rows back in any particular sequence. I wouldn't worry about that since the autonumber essencially has no meaning anyway. When you use increment, there is a meaning and the autonumber shows the order in which the rows were added but with random, there is no way to identify insert order.

 

[Minty]

I use auto numbers for quite a few visible keys, but they really do mean nothing to the end-users.

A Quote_ID for instance, is a simple number, as long as it's unique who cares? I generally use SQL as a backend so the weird corruption I've seen in Access doesn't normally occur so the number sequences stay relatively sensible.

If you really need sequential incrementing unique numbers then you need to jump through some hoops to get them.

 

[Pat Hartman]

Although, I frequently make the autonumber visible on forms, this is more for my benefit than for anyone else's. For things like QuoteID or InvoiceID or anything that will be printed on a document, I leave the autonumber as the PK but I generate a fixed length number that the user is more comfortable with. That way I can start it at 1000000 for example so it doesn't need leading zeros. This also lets me use character prefixes if the user wants them. Then I make a unique index on the generated sequence number to prevent duplicates.

 

[MajP]

There is a big difference if the keys were always random, or if they just started becoming random. The first is fine the second is alarming.
If this is a new problem then it is pending corruption and needs to be fixed. If not there is no problem and it is working fine. If a new problem.

If it needs to be fixed or if you want to then simply create a new field and make it long integer. Call it OldPK. Copy the current PK into this this field. Now make a new field and make that your PK and make it an autonumber. You might have to delete any existing relationships in the relations window. Now you can simply do an update query to all tables that were previously linked to the main table. Update the old FK to the new PK where the old FK equals the old PK.

 

[oleronesoftwares]

You might have to delete any existing relationships in the relations window. Now you can simply do an update query to all tables that were previously linked to the main table. Update the old FK to the new PK where the old FK equals the old PK.

Are you sure this will not distort some records or even further damage the database?

 

[isladogs]

A further voice against changing now.
Access will definitely not allow you to change a random PK field to incremental once it contains records.
Trying to change anything at this stage will make you a huge amount of work with little benefit.

 

[MajP]

Are you sure this will not distort some records or even further damage the database?

Yes.