Solved Starting Appended Data at 1 (1 Viewer)

[jwcolby54]

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.

It can be done. I have done it. It is not particularly useful, and you probably don't want to go doing that. Except in one case...

Suppose that you archive records in a table, keeping the PK out in the archive (which you BETTER do!). Then you delete the original records in the original table. Now someone comes along and tells you that you need to recover those records back into the original table.

No problem says I, write a select query to grab the specific records including the autonumber pk. Then use that source query to append those records back into the original table including the pk.

Voila, you have just inserted records back into the "hole" and the same original PKs are valid in the child records, which you are almost certainly also recovering into their respective tables, which also created "holes" as you archived them.

What you will need to do however is tell the autonumber to pick up after the LAST NUMBER in the PK field. If you do not do that the autonumber code will continue filling in the hole with autonumbers. But the holes "belong" to other archived records.

 

[gemma-the-husky]

@jwcolby54

I am not trying to do anything. I was just discussing some points raised in the thread.

I never try to ascribe "sense" to an autonumber. If I want an intact sequence, I use an appropriate technique.

 

[gemma-the-husky]

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.

In Access you can just write an append query and force the ID to be written as any arbitrary value, such as 3.

I think SQL server for instance won't do this behaviour .

 

[jwcolby54]

In Access you can just write an appendix query and force the ID to be written as any arbitrary value, such as 3.

I think SQL server for instance won't do this behaviour .

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?

 

[DHookom]

In Access you can just write an appendix query and force the ID to be written as any arbitrary value, such as 3.

I think SQL server for instance won't do this behaviour .

Thanks, I just tested this myself to confirm this could be done,

 

[gemma-the-husky]

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?

An arbitrary number. A specific number rather than the next autonumber provided by the seed.

As I say, I don't think you can do this with SQL Server.

 

[jwcolby54]

Thanks, I just tested this myself to confirm this could be done,

What can be done? Access or SQL Server?

 

[DHookom]

With SQL Server, I believe you can insert identity by issuing the command:
IDENTITY_INSERT to ON.

SET IDENTITY_INSERT Products ON;

Remember to set this off when finished.

 

[Pat Hartman]

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.

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.

 

[Pat Hartman]

YOU ARE MAKING THAT THE MEANING.

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.

 

[jwcolby54]

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.

 

[jwcolby54]

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.

True!!!

 

[Pat Hartman]

Someone might decide to fill in the holes with new records. Suddenly the autonumber no longer represents order.

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.

 

[jwcolby54]

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.

true true and true. OTOH having an inserted date field, automatically set to now() as any record is inserted....

And really reiterate to everyone involved that an autonumber field is just the PK of the table. Nothing more.

 

[GPGeorge]

This bit.



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.

 

[GPGeorge]

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.

 

[GPGeorge]

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.

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

 

[DHookom]

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

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.

 

[jwcolby54]

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.

 

[Pat Hartman]

What you will need to do however is tell the autonumber to pick up after the LAST NUMBER in the PK field. If you do not do that the autonumber code will continue filling in the hole with autonumbers. But the holes "belong" to other archived records.

Nope. That's not what happens. The seed doesn't change after the append.