One of the shibboleths of Access design is that Autonumbers make good surrogate Primary Keys because a new one is always generated whenever a new record is started in a table. They are, therefore, guaranteed to be unique, or so goes the belief.
Moreover, because their New Value property is by default set to increment, i.e. to be sequential, they are also counted on to increment in an unbroken pattern, one at a time. That's generally what happens.
Unfortunately, as any long term Access developer can tell you, there isn't any such guarantee in the real world. There are at least two ways in which one or the other of these confidences can be betrayed.
- Autonumbers can be generated with gaps in the sequence
- Somewhat frighteningly, they can be duplicated.
Gaps appearing in an AutoNumber sequence is familiar to all Access users. It happens either when an existing record is deleted or when a new record is started but abandoned before it is saved. Because Access is designed to generate new, previously unissued values whenever a new record is started, and because it drops those values when the record is later deleted or not saved, any such values are lost, creating gaps in the sequence. Other than the discomfort some users feel when they see such gaps, it's generally a case of "No harm, No foul." Although there's no universal truth here, most developers feel users should never even SEE the AutoNumber Primary Keys in tables, anyway. What they can't see can't hurt them.
The second way is not unfamiliar, although fortunately quite a lot less common: corruption. Corruption occurs for several reasons. Although this article is getting a bit long in the tooth, Allen Browne has published one of the most comprehensive discussions of the topic I know about. It's not the main focus of this discussion, so I'll leave it to you to you to follow up, should you be interested in learning more about corruption of the database type.
Suffice it to say that Access can abandon corrupt records--including their AutoNumber values--when you do a Compact & Repair on that database. Both the record itself, and that particular AutoNumber value, are lost. The lost AutoNumber value is in itself, not a big problem. Just like with lost or manually deleted records, the loss of the AutoNumber should result in no harm. The loss of the record, of course, can be very harmful, especially in the absence of a backup of that record.
As an aside, there are documented cases of such corruption being persisted through several generations of backups, so that recovering a good copy is no longer possible. That's another story for another day.
There is still another way, though, in which AutoNumbering can go very, very wrong, and that's really what I mostly want to talk about today.
This problem came to light in a recent incident where a friend asked me to lend a hand in a project. He was leaving on vacation and didn't think he was going to be able to wrap it up in time.
The database in question was split, but a couple dozen users were all sharing a single mdb Front End. This is as close to a universal truth as we get in the Access world:
Sharing a single FE file across a LAN is bad juju.
Shared front end mdbs and accdb files corrupt, sooner or later. Shared back ends can also corrupt, but fortunately that's less common.
We rescued them, doing a compact and repair on the back end and copying the front end to each user's desktop. That took all morning, walking from workstation to workstation, booting the user off while we copied and set up the file.
Then, we had the owner check. Sure enough one record was gone. ONLY ONE! Cool beans. All we need to do is reenter that one record and make sure all of its child records are updated to the new Primary Key. Not so fast, bubba. Whoever designed this puppy decided to use this very same AutoNumber PK for their Work Order Numbers. It's even printed on the certification documents they send to customers. Now, that right there is a problem. It's the main reason why experienced Access Developers don't show users Autonumbers for any reason. Just imagine. If we had another field in that table called "WorkOrderNumber" with a separate AutoNumber Primary Key field, it would have been a matter of mere seconds to add a new work order, manually give it the missing Work Order Number and then update those child tables to match. Well, no more than a couple of minutes, anyway. But the original developer didn' t do that. He used that doggone AutoNumber.
So what is that message going to sound like? "Um, sorry, but we lost your data and that certificate is no longer viable. Here's a new one. This one is DEFINITELY good. Trust us."
Doable but very, very undesirable.
So, I very cleverly came back the next workday to reinsert that lost record. Here's an interesting, and probably not widely known fact about Access Autonumbers: you CAN manually insert one specific value into an AutoNumber field. Of course, you can't duplicate an existing value in a Primary Key field, but, it's not a problem to reinsert one that was lost due to deletion or corruption. To quote one of my favorite tv shows from the 60's--Surprise, Surprise. That is not the only problem to look out for. Here's what happens, where I went into the ditch and the simple way I was able to recover, but not without significant loss of face in the process.
When Access looks for the next AutoNumber value to insert into a new record, it DOES NOT USE the largest existing value to calculate the next one. Access DOES USE the most recently entered value to calculate the next one.
Oopsie.
When I reinserted that lost AutoNumber value to restore the Work Order Number, it reset that starting value. Unfortunately for all concerned, a lot of people were already in the database, busily entering new work orders. So, rather than having the owner boot everyone one so I could do a compact and repair on it, I let it go, reasoning, incorrectly, that we'd just pick up where we left off.
The trap was set. There were already several new records with higher AutoNumber values which had been entered between the time I started and the time I finished, no more than 10 or 15 minutes. The next two or three new records were able to reuse an incremented AutoNumber values previously lost due to deleted or abandoned records, but as soon as it tried to generate one that WAS in use, it blew up.
Well, not literally, but it might as well have. It simply raised the error that Access couldn't add that new record due to a conflict with an existing Primary Key value. Bad juju.
So, more recovery work, with the correlative loss of confidence. And this time, when the data was recovered, I DID have the owner boot everyone out and do a Compact & Repair on the Back End. Testing demonstrated that it was now using, correctly, the last entered value as the seed for the next available value, based not on the reinserted value, but on the last new value generated by entering a new work order (I hope that makes sense). We burned two or three new Autonumbers testing to be sure, which must cause heart-burn to their people who look for gaps in Work Order Numbers on printed certificates, but that one's treatable with anti-acids, and not a problem for customer relations.
All said and done? I would have been ahead to tell them to recreate that particular work order, with a new Work Order Number and regenerate the certificate to be mailed to their customer.
Either that, or do the job right and finish up every significant database change with a Compact & Repair.
It's just that simple, I guess, when you stop to think about it. Do a Compact & Repair and do not call me in the morning.
