Problem with keys

[MajP]

I checked with the person who built it and for sure the numbers were used to be arranged in ascending order from 1

If that is true that would concern me. If the PK went from incrementing to random that would hint to me the database is getting corrupted.
I would do the following.
1. As previously mentioned backup the existing PKs in a new field. Then you have them and can create a new PK and update all the old foreign keys.
2. Create a clean blank database
3. Import all your tables and compact and repair the new database. If it will not compact and repair then do the following first.
4. Create the new autonumber field.
5. Copy the old PK into the new field which is a number field (not autonumber)
6. Make your new autonumber field in 4 the PK.
7. Create update queries to make all the old FK linked to the new PK (come back if you need help)
8. Delete the old PK field
9. Import all the forms, reports, queries, and modules.
10. compact and repair

 

[Pat Hartman]

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.

I guess you didn't see my step by step description.

 

[MajP]

I guess you didn't see my step by step description.

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

 

[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.

 

[Pat Hartman]

George, you are probably correct. Someone accidentally or on purpose changed from increment to random but we don't know that. We only know that it happened. They were originally sequential now they are random. No explanation of how or when. If the autonumbers are displayed anywhere, seeing very large negative numbers will be disconcerting at best so I would renumber the records for that reason.

If the change was due to corruption, it needs to be fixed. So, since we can't determine if the change was corruption or an accidental property change what do you recommend? Doing nothing until something worse happens?

 

[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.

 

[Pat Hartman]

You can play it safe and assume it is incipient corruption.

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

 

[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.

 

[Pat Hartman]

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.

 

[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?

 

[Pat Hartman]

Yes, random autonumbers are still available but not the synchronize feature.

 

[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?

 

[Pat Hartman]

Yes. Replication, It was a long time ago and I forgot the technical name. Replication synchronizes two disconnected databases when they "connect" again. It worked very well for adding data but synchronizing updates was a whole different problem. Only humans could decide which update should take precedence. You actually need a third copy of the database to automate reconciliation. That way, Access would know what the values were when the replica was last in touch with the mother ship. If only one of the current two was different, then that version would become current but if both versions had been changed since the last synchronization, then only a human could decide unless the compare was done field by field and merged. So the master had fld1 changed but the replica had fld2 changed. Logically there is no conflict and both could be accepted. We won't talk about the potentially broken business rules this might cause.

I looked at the feature back in the 90's for a client who wanted to do off line data entry. We decided that we could do it in a limited way to handle new data and I built the replication process myself because I needed to ensure that only new data went from the replica to the mother ship. After sync'ing, the replica was replaced with an updated version of the master. New data from a replica is fairly easy. Updates are a rabbit hole.

In the end, there were too many downsides to overcome the benefits, so MS discontinued the feature.

 

[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