I would think it could. Restoring archived records is a valid use for this. That is not an "arbitrary" value, but how is a database engine supposed to know what the administrator intends?
I would think it could. Restoring archived records is a valid use for this. That is not an "arbitrary" value, but how is a database engine supposed to know what the administrator intends?
No. When the autonumber is defined to be sequential, it does represent the order in which rows were inserted. When the autonumber is defined to be random, it is random and offers no additional information beyond uniqueness. I agree, the typical use of an append query that includes an autonumber is to restore deleted records or to convert tables from an old system to the new one when the tables in the new application are similar enough to be initially populated with existing data.
>>>it does represent the order in which rows were inserted
No it represents the PK of the record. You are interpreting the fact that they are in order to represent order. It might, but it might not. Someone might decide to fill in the holes with new records. Suddenly the autonumber no longer represents order.
The autonumber PK was created long ago to define an absolutely unique PK. Nothing more. Folks were using candidate fields such as SSN as the PK and the quite obvious failings of that pointed out the need for the autonumber. It was never intended to represent "order of insertion". YOU make it that, because apparently you forgot to add an InsertionDate field and now, 10 years later, that is the only way you can determine the insertion order. I have no need for that because I will have an insertion date field from the gitgo. Mine will work when someone "fills in the holes" Yours will fail.
No need for a demo. Use any table you have. The ONLY way to insert a record with a specific autonumber though is by using an append query. You can't just type one in using a form or by editing the table or a query. It MUST be with an append query.
That is true but luckily only people who know nothing about database design try to fill in the gaps of an autonumber. Using an append query to restore previously deleted rows, isn't the same thing as using an append query to fill in the gaps. Even novices eventually give up on this because it is harder to implement than they think so they generally can't figure out how to do it themselves and we try to not give them the bullets for their guns.
That's not correct. The order of the autonumber IDs corresponds to the order in which records were inserted. It's not random. If you insert records to fill gaps, then that's no longer correct. That's the point I was trying to make.
Yes, but as I pointed out in my two examples, the order in which records are inserted is itself not inherently important. The only way AutoNumbers can be reliably used is to uniquely identify records.
Relying on the sequence of AutoNumber values to be of any importance is a subtle, but important, logic error.
I had not known you could insert an Autonumber between two other values. For instance if you have five records with values 1-5 and delete the 3 record, I wasn’t aware you could insert a record with 3 as the autonumber. I would appreciate if some could provide a small database with a demo of how this is done.
Maybe later today I'll have time to code up an example. However, I will say that I obtained one of my most favorite client projects by demonstrating to them that it can be done to recover corrupted records. They had paper copies of invoices from which they could retrieve the AutoNumber PKs, and other details. All we had to do was create the query from those values.
Maybe later today I'll have time to code up an example. However, I will say that I obtained one of my most favorite client projects by demonstrating to them that it can be done to recover corrupted records. They had paper copies of invoices from which they could retrieve the AutoNumber PKs, and other details. All we had to do was create the query from those values.
Thanks George, I had already successfully tested this #46. Since retirement four years ago I spend more time on my iPad than my laptop. This is more convenient but doesn’t allow me to futz with Access.
I keep my very powerful windows laptop mostly to allow me to play around with Access. It is Windows 10. It will never go to Windows 11. I woul love to run a VM on this laptop where if I caught some nasty I could just start a fresh VM. Sadly I don't have the expertise to move this instance into a vm image, back it up somewhere, put Linux Mint on it, and mount that Windws VM. It can be done, but I can't do it.
@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 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.
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.
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.
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.
Code:
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!
