Solved Starting Appended Data at 1 (2 Viewers)

[jwcolby54]

Here's a simple illustration of the method with one table and one append query.

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

 

[jwcolby54]

Here's a simple illustration of the method with one table and one append query.

 

[jwcolby54]

 

[jwcolby54]

I used George's little demo to run his query, inserting the PK number 2. I then entered G and H successfully. Notice that the PK continued to increment the counter from 2 -
G: 3,
H: 4
However when I tried to enter I : 5, it stopped me from entering that record because the autonumber was trying to put 5 in the PK, creating a duplicate key.

 

[jwcolby54]

In #62 I said that it would do this except that I thought the PK mechanism was smart enough to go find the last PK value and increment that. IOW I thought when I entered I: 5 it would know it was "out of room" and go find the highest PK... 6, increment that, and start with 7. Instead it tried to continue in the sequence with 5. When it tried to do the save, it "discovered" that 5 was already used and simply threw an error.

 

[jwcolby54]

Sometimes it is important to know stuff just because. If you ever want to insert records to "fill in a hole", recover archived records etc, you probably want to run another query that finds the highest PKID, increments by one, and inserts that record as well. This allows the autonumber to continue on its merry way.

 

[Pat Hartman]

I don't remember ever having this problem so I thought the append didn't affect the seed. Apparently it does.

I would not just insert an arbitrary "empty" record. That would probably violate your validation rules assuming you have any.

Use this to reset the seed.

Function ChangeSeed(strTbl As String, strCol As String, LngSeed As Long) As Boolean

'''''' requires reference to Microsoft ADO Ext. 6.0 for DDL and Security
'''''     You must pass the following variables to this function.
    strTbl = Table containing autonumber field
    strCol = Name of the autonumber field
    LngSeed = Long Integer value you want to use for next AutoNumber.
'''
    Dim cnn As ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim col As ADOX.Column
'''
''' '   Set connection and catalog to current database
    Set cnn = CurrentProject.Connection
    cat.ActiveConnection = cnn
    Set col = cat.Tables(strTbl).Columns(strCol)
    col.Properties("Seed") = LngSeed
    cat.Tables(strTbl).Columns.Refresh
    If col.Properties("Seed") = LngSeed Then
        ChangeSeed = True
    Else
        ChangeSeed = False
    End If
    Set col = Nothing
    Set cat = Nothing
    Set cnn = Nothing
End Function

 

[jwcolby54]

I don't remember ever having this problem so I thought the append didn't affect the seed. Apparently it does.

I would not just insert an arbitrary "empty" record. That would probably violate your validation rules assuming you have any.

Use this to reset the seed.

Function ChangeSeed(strTbl As String, strCol As String, LngSeed As Long) As Boolean

'''''' requires reference to Microsoft ADO Ext. 6.0 for DDL and Security
''''
    You must pass the following variables to this function.
    strTbl = Table containing autonumber field
    strCol = Name of the autonumber field
    LngSeed = Long Integer value you want to use for next AutoNumber.
'''
    Dim cnn As ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim col As ADOX.Column
'''
''' '   Set connection and catalog to current database
    Set cnn = CurrentProject.Connection
    cat.ActiveConnection = cnn
    Set col = cat.Tables(strTbl).Columns(strCol)
    col.Properties("Seed") = LngSeed
    cat.Tables(strTbl).Columns.Refresh
    If col.Properties("Seed") = LngSeed Then
        ChangeSeed = True
    Else
        ChangeSeed = False
    End If
    Set col = Nothing
    Set cat = Nothing
    Set cnn = Nothing
End Function

Appending back in archived records is the easy part. Figuring out how to reset the seed is more problematic. As you mentioned there may be other things that are expected of a "blank record". It seems that your way actually sets the seed property directly. Nice!

 

[Pat Hartman]

I think it works with SQL Server also.

 

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

 

[Pat Hartman]

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.

 

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