Solved Starting Appended Data at 1

[GPGeorge]

@GPGeorge can you test what happens if you add a single record into a multi record hole? IOW delete 5 records, then fill in the first hole, then add another record manually. IIRC the autonumber picks up after your append and adds one to whatever that PK you just placed in with a query.

PK:

01 to 100 exists
101-105 are a hole
106 - 110 exists.

Use your query to add 101 into the hole. Add a new record allowing the autonumber to do whatever it wants. What is the autonumber value it places into the table? Is it 102, the number after yours? Or is it 111, the number it would have been if you had not inserted your record?

I seem to remember that it adds 102, 103, 104 and 105. When it runs into 106, it "jumps" to the last (highest) value plus 1. In this case it would go from 105 to 111.

Is that clear as mud? I haven't actually done any of this for 15 years so maybe my memory is hazy.

I uploaded the little test accdb. So far as I understand and observe, your description matches what I see.

The query inserts a record into the table using the specified value for the AutoNumber PK field, with one exception. That is, if the attempted insertion would duplicate an existing PK value, it fails. As we'd expect.

When you resume adding records normally, i.e. in a datasheet or using a form, or appending a record without specifying the PK, it picks up from that last entered value, regardless where it falls in the overall sequence, and regardless of how it was inserted, via the query or via a form, etc.

For example, I append a record with PK value 20000 specified using the query. Then I append a record with PK value 10000 using the query.
When I add another record, using the datasheet view of the table, the assigned PK is 10001, the next value following the most recently used value.

 

[jwcolby54]

Now that we've all confirmed this behavior, has it always been so? I don't remember this because it sure would have broken my application. I used this method to allow a client to delete and reload my original set of test data so each new training class could use the original training materials. The training "audit" was just an ordinary audit amongst the production data.

If this is something new, I think it is a bug.

I remember this from back in the early 2000s when I actually Access'd for a living.

 

[jwcolby54]

I uploaded the little test accdb. So far as I understand and observe, your description matches what I see.

The query inserts a record into the table using the specified value for the AutoNumber PK field, with one exception. That is, if the attempted insertion would duplicate an existing PK value, it fails. As we'd expect.

When you resume adding records normally, i.e. in a datasheet or using a form, or appending a record without specifying the PK, it picks up from that last entered value, regardless where it falls in the overall sequence, and regardless of how it was inserted, via the query or via a form, etc.

For example, I append a record with PK value 20000 specified using the query. Then I append a record with PK value 10000 using the query.
When I add another record, using the datasheet view of the table, the assigned PK is 10001, the next value following the most recently used value.

This is all true. It will continue adding in records until it hits a record "in the way" and then it will fail with an error. @Pat Hartman had very cool code to reset the counter to the last used +1.

Obviously if one is recovering archived records, one can't just let the autonumber continue filling the hole with new data, or the next time you want to restore archived records there will be no hole...

 

[GPGeorge]

This is all true. It will continue adding in records until it hits a record "in the way" and then it will fail with an error. @Pat Hartman had very cool code to reset the counter to the last used +1.

Obviously if one is recovering archived records, one can't just let the autonumber continue filling the hole with new data, or the next time you want to restore archived records there will be no hole...

In the case I cited earlier in this thread, that was exactly the case.

Long story short, the client printed paper copies of invoices. The Invoice Number was, in fact, the AutoNumber Primary Key of the table. For auditing purposes they HAD to account for every invoice number. (The CFO wants to know what happened to invoice 21888....).

Periodic corruption in the Access database led to lost records at random intervals. Fortunately, the fact that they had paper copies of every invoice meant all I had to do was find and delete the corrupted record(s) and add them back from the paper invoice, using the query method. As far as I recall, there were never more than a couple of records in a row that needed to be recovered that way.

As an aside, it turned out that their network guy had draped the network cable across the top of a fluorescent light fixture to get it to an office away from the main work area. (No point climbing up in the ceiling and all that dusty work.) Whenever the light was turned on in that area, it blitzed the cable, causing drops in traffic through it. If it happened during a write to the Access back end, bye-bye record(s).

But the point is that in that case in particular, it was a matter of replacing corrupted records on a one-for-one basis.

 

[GPGeorge]

any comment on whether you think this behavior is a bug.

I don't know if you are addressing me, but I do not consider this a bug. It seems quite sensible to me, in fact.

If Access allowed you to duplicate a value in the AutoNumber fields using this technique, I would consider that to be a bug. Or if it only allowed values greater than the greatest existing value, that also would be unexpected, IMO.

I'm curious, though, if others see it differently, and why.