Autonumbers (Access 2000)

Topic

New Reply

I have a customer enquiry database that assigns an autonumber to each new case – each case therefore has a unique reference. I recently added a new field to the data table and then “paste appended” this field with data copied out or Excel. Anyway – as is my specialty, this didn’t work the first time and a “paste error” table was created. It pasted OK at the next attempt, but now any new record has an “autonumber” hundreds past the last record due to these paste errors being counted. (E.g. last record was 1800 – new records assigned 4400)
I have tried to change the next number in the table so that i go back to the sequence, but it only seems to work for one record, and then goes back to high numbers. (Used the ‘Help’ available on the help menu and the Microsoft site – used a temporary table with the number i wanted to start at and an Append Query)
Is there anyway of getting the numbers back in a sensible sequence (i.e. next record will be 1801)?? I cannot affect the numbers already in the database however, as some records have been deleted, and they do not run from 1 all the way to 1800 (i.e may only be 1750 records).
Thanks for any help,
Alastair.

Reply | Quote

Replies

One of the guidelines for using AutoNumbers is that they don’t really mean anything. As such that means that they also may not use all the numbers sequentially. If you really need a key that means something, you should probably look at creating your own key using a method such as taking the maximum of the current records and adding one, or alternatively storing the next number in a table, looking it up, and then incrementing the number and resaving it in the table.

As to your specific question, the only good way to reset the numbers is to add a new autonumber to the table, then update any linked records with the new number (that can be a MAJOR pain), and then delete the old one. If you have referential integrity constraints set, that can also be a significant challenge.

Reply | Quote

Alastair

The only way I know of resetting the Autonumber field is to compact the database. This should mean that it will start at 1 above the highest existing number. There is no easy way within Access to use any existing unused numbers below the highest number. The only way round that would be to copy the whole table into Excel, update the reference numbers where you can, and then copy the whole table back again. This assumes that changing the reference would not have any impact elsewhere in the database.

HTH

Bodders

Reply | Quote

Thanks for the tips. I have tried copying the table as suggested already, but the autonumber still remembered the 4400 (ish) number and continued from there. Likewise with compacting the database.
I have thought of creating another table with the numbers stored in, or even some way of running the append query to add one to the last number when creating a new record….all will need some thought and time on my part if thay are at all possible! My other thought was to start with a fresh table for the records to go in, starting the autonumbers at the next number – but then i have queries and graphs based on the original table and would need to look at two tables rather than one and that didn’t work! For now i think i will just make do with having a big gap in my numbering.
Thanks for the help,
Al.

Reply | Quote