Add Autonumber

[unknown17]

Apologies if this has been asked before:

I have a small table in access 2003 with autonumbers. I managed to delete two records by mistake (autonumbers 23 & 24).

Is there any way to add them back in?? It is important as these numbers have been specific to those records for a couple of years.

Thanks in advance!

 

[Dennisk]

Firstly Autonumbers should not be exposed to users as they will use those numbers on case notes, invoices etc. Autonumbers should be internal to Access and only used As Primary keys.

there is a standard technique to reset an autonumber.
Take a copy of the table and change the autonumber field to long interger and add a record with the old autonumber field set to the number-1 you wish to start from.
Then Copy that record to your table.
Delete the dummy record and the autonumber should continue from the number of the record entered.
you may be able to adapt this technique. Good Luck

or you could convert your autonumber to a user generated autonumber (plenty of examples here) then you could slot your deleted records back in with the original number.

Failing the above why not restore your db?

 

[gemma-the-husky]

you can also use an append query to append specific numbers into an autonumber field

 

[doco]

Failing the above why not restore your db?

Doesn't this require a backup?

 

[Dennisk]

doco

I ALWAYS have backups of all my work going back to the year dot.

 

[doco]

Dennisk: It wasn't about you man.

I have a small table in access 2003 with autonumbers. I managed to delete two records by mistake (autonumbers 23 & 24).

Is there any way to add them back in?? It is important as these numbers have been specific to those records for a couple of years.

One must only assume if something was dropped accidentally; and then in wonder how to recover; and was unaware of restore; then possibly a backup was not performed. All too common a mistake. A 'couple years' and no backup

 

[Dennisk]

Even if backups are taken that does'nt mean you can restore. Had that happen to twice to me in the last 4 years. Once we had to buy a recovery program to get one access BE back as the network guys just mumbled about old or not enough backup tapes and sorry but can't find your db.

 

[ByteMyzer]

doco,

The fact that you were able to delete those records indicates that you do not have referential integrity enforced, so the following steps should work:

1) Create a copy of the table (Structure AND Data)
2) Delete all records from the original table (DO NOT DELETE THE TABLE ITSELF).
3) In the table copy design, change the Autonumber type to Number.
4) Save the changes to the design.
5) In the table copy, enter the information for your missing records (23 and 24).
6) Compact the Database (Tools > Database Utilities > Compact and Repair Database...).
7) Open the table copy, press CTRL-A, then press CTRL-C to select and copy all the records.
8) Open the original table, press CTRL-A, then press CTRL-V to paste the records back into your original table.
9) Delete your table copy.

 

[doco]

ByteMyzer:
Good info Byte, but it wasn't me.

Dennisk:
I always backup to MY OWN media. The scenario like what you mentioned only happend to me ONCE. I no longer (if I have a choice) depend on anyone to maintain my stuff. Network guys (and gals) are oftentimes notorious about caring only for the network and not particularly what's on it - unless of course it is intrusive.

 

[ByteMyzer]

ByteMyzer:
Good info Byte, but it wasn't me.

Whoops, you're right, that should have been directed to unknown17. See what happens if you ask a question in the middle of someone else's thread? Someone reading the middle of the thread without re-reading the beginning can mistake you for the person who asked the original question.

Also, what's up with these user accounts like unknownxxx and Anonymousxxx? Don't users believe in using real usernames anymore?

 

[neileg]

Also, what's up with these user accounts like unknownxxx and Anonymousxxx? Don't users believe in using real usernames anymore?

I assume that was irony? ByteMyzer is hardly any less opaque!

 

[ByteMyzer]

I assume that was irony? ByteMyzer is hardly any less opaque!

Nonsense. Anyone who sees a post by ByteMyzer, neileg, doco, DennisK or gemma-the-husky can mentally associate the identifying User ID with an individual. A moniker like unknown17 or Anonymous123 is only unique to the server, and does not strike an outstandingly unique association to an individual, mentally.

Also, someone who chooses a User ID like ByteMyzer, neileg, doco, DennisK or gemma-the-husky, wishes to be known by a unique identifier. Conversely, someone who chooses a User ID like unknown17 or Anonymous123 clearly does not wish to stand out in a crowd.

Taking this into consideration, I see no irony here, neileg. Now, putting that aside, I don't suppose you have a useful solution to contribute to the question posed by this thread's originator, do you? I posted one, but perhaps you have a better one. I'd love to see it; I'm always looking to expand my knowledge.

 

[RoyVidar]

<grin>is this what they call the natural vs surrogate debate?</grin>

 

[gemma-the-husky]

to return to the point of the original post

the errant numbers can be simplt stuffed back in with a query, directly setting the autonumbers to the required values - you don't need to add extra fields etc

 

[neileg]

ByteMyzer, I don't propose to extend this debate, you expressed your view, I expressed mine.

The proper answer to the poster's original question was provided by gemma-the-husky. I didn't feel I needed to repeat this.