Problem with keys

[oleronesoftwares]

Sorry missed that. However, creating a new db and importing to a new db should still be done.

Both your suggestion and @Pat Hartman's suggestion is good,the OP should be ready to do a meticulous work to resolve the issue.

 

[GPGeorge]

I remain unconvinced that there is any reason to do anything regarding random values in this Autonumber primary key.

First, the assumption that the change happened "by magic" seems to underlie a lot of the thinking about that. I doubt that for two reasons.

• It's one of two options. The change could have happened when anyone at some point in the past wondered what would happen, or when someone, at some point in the past inadvertently changed it. Belatedly that person learned there was no going back.... One thing for sure is that some other change didn't "just happen". Corruption doesn't magically result in a valid implementation that has functioned for some unknown period of time.

And that brings up the second point. It's been in this alternate state for a while with no one reporting problems with data.

I recently started working on a database that i believe no one has touched for several years.

That alone should give pause to any plan to "do something about it". If it ain't broke, don't fix it. Of course, I'm assuming here that, by "no one has touched", the OP means no developer had modified it for several years, although users have been using it in their day-to-day work. That implication is strong through the rest of the thread regarding multiple tables with the random values.

And third, if this difference only came to light when the OP was tasked with "working on a database", then the strong implication again is that no user has ever noticed or cared that the Autonumbers are random, not incrementing.

In summary, the use of random vs incremental values in and of itself is a benign choice. The fact that 99.9% of the time we see incrementing values in Autonumbers doesn't invalidate the remaining 0.1% of the times when random values are used. Change it if you have the time and money (i.e. the value of your salary) to invest and are willing to do that. If you have to explain to someone that you devoted some percentage of your budget to making a change that produced no value to the project, maybe that's not a good plan.

 

[MajP]

t's one of two options. The change could have happened when anyone at some point in the past wondered what would happen, or when someone, at some point in the past inadvertently changed it. Belatedly that person learned there was no going back.... One thing for sure is that some other change didn't "just happen". Corruption doesn't magically result in a valid implementation that has functioned for some unknown period of time.

I was mistaken on this. I did not think it was possible to change an incrementing key to a random so I assumed there was an issue. Going from increment to random is possible. You cannot go the other direction. You cannot start with a random key and change to incrementing.

But keys do in fact go bad. And if this happened on its own then I would be concerned and I would personally fix it. If it did not happen on its own then no issue. I use to see this a lot more on older dbs. Do not see it much anymore.

Microsoft Access tips: Fixing AutoNumbers when Access assigns negatives or duplicates

Identifies why Microsoft Access assigns duplicate AutoNumbers or wildly inappropriate values for AutoNumbers, and explains how to correct a database that already has these problems.

allenbrowne.com

 

[GPGeorge]

As noted, this is not a case of "keys going bad". That's never been part of the initial scenario. It was proposed by those of us who've seen that happen. The method of generating new values for a perfectly normal Autonumber field changed. No "bad keys" are reported.

Mysterious things can, I suppose, happen. But it's a lot easier to believe that someone--a real person using this mdb--wanted to see what would happen, learned the hard way, and then couldn't put it back.

 

[GPGeorge]

You can play it safe and assume it is incipient corruption. It depends on how long this relational database application has been in production and what problems have been encountered to date and whether the change happened last year or three years ago. I would err on the side of caution if this were a client, and ask them to spend their money changing it just in case. But do so knowing that it is probably an investment in "insurance" and not a correction of a problem.

 

[HealthyB1]

And that is exactly why I gave the OP detailed instructions on how to rebuild the autonumber.

FWIW:- I seem to remember having this problem about 15-20 years ago. I wanted to have a database on the main PC in the office and a copy on my laptop that could be used on customer visits. The intention was that when they would re-connect on the network they would synchronize with each other automatically. I can't remember if I used a package to allow this flexibility between the PC's? Anyway it worked ok, but the by-product was the primary key on the main files with indexes ended up looking the same as the OP has described.
I ran with it for years as it worked ok and there were no errors, but eventually cleaned it up by using a method similar to Pat's suggestion. I seem to remember that it did take a lot of work for a cosmetic change.

 

[isladogs]

Pat
Not sure which feature you're referring to. As shown in the screenshot in post #5, random autonumbers are still available and AFAIK were never dropped. Are you referring to something else e.g. replication?

 

[isladogs]

Which version(s) did have that synchronise feature?

 

[GPGeorge]

FWIW:- I seem to remember having this problem about 15-20 years ago. I wanted to have a database on the main PC in the office and a copy on my laptop that could be used on customer visits. The intention was that when they would re-connect on the network they would synchronize with each other automatically. I can't remember if I used a package to allow this flexibility between the PC's? Anyway it worked ok, but the by-product was the primary key on the main files with indexes ended up looking the same as the OP has described.
I ran with it for years as it worked ok and there were no errors, but eventually cleaned it up by using a method similar to Pat's suggestion. I seem to remember that it did take a lot of work for a cosmetic change.

You were using a random autonumber since that is the only way synchronization can work. Access used to actually support this feature. The problem is that it is not 100% reliable and since there is no way to actually make it 100% reliable, MS got rid of the feature.

Which version(s) did have that synchronise feature?

The last time I remember it was in A2003. I picked up a project moving the client's mdb to SQL Server and that was one of the pain points for them that prompted the migration. The replication piece was getting cumbersome as their staff grew. Access 2010 came out while I was on that project, so time frame was 2009-2010.

Again, replacing those Primary Keys is a cosmetic change, IMO, and possibly justifiable on the basis of "insurance" that it wasn't corruption.

 

[isladogs]

So it was replication as I suggested in post #33?

 

[isladogs]

I remember many of the issues associated with replication and wasn't at all unhappy when it was scrapped in A2007.
The fact that it needed about 13 system tables to work says a lot ...
I may have missed some ...

That info comes from my article on system tables:

Purpose of System Tables - Mendip Data Systems

Database applications for businesses, schools and individuals

mendipdatasystems.co.uk