Autonumber reset (1 Viewer)

[KelMcc]

In Access 97, is there an easy way to reset an autocounter?

Or, maybe, more generally speaking, is there a way to "reset" your database? Meaning, I've done all my testing and I want to clear the data, reset counters, etc... Is there a simple command for this? I've looked around some and not found anything in the Help or by searching these forums.

I guess, I could always break the links to the field change it to "number" then change it back? That seems icky, though.

 

[Pat Hartman]

There are at least two ways that I know of. Both require the table to contain 0 rows. One is to compact the db. That will reset the value of ALL autonumbers to n + 1 with n being the highest value in the table. So if the table is empty, n would be zero and so the next number to be assigned would be 1. The other is to open the table in design view, delete the autonumber column, save the table, then paste the autonumber field back and save the table again.

 

[KelMcc]

Yeah, I had to wipe the db, unbreak the links then save the autonumber field as something else, then swap it back and that worked. Fortunately, it is still in test mode so I didn't lose any data of value.

Thanks Pat.

 

[Fastone]

I will have to do this soon as well. Is there really no easier way (I'm using 2000)? Or did Microsoft forget to put it in.

 

[Pat Hartman]

Microsoft didn't forget anything in this particular case and in fact, Microsoft's implementation of the Autonumber is much smarter that that of several other RDBMS' implementation of Identity columns. Autonumbers are intended to provide a unique identifier for a row in a table. They serve no other purpose and therefore should not require any manipulation. What difference does it make what your lowest starting value is if the number itself has NO meaning except to uniquely identify a row in a table.

KelMcc, I don't understand why you would have to break links to other tables. If your RI settings are correct, deleting a row from a parent table should delete dependent rows from all child tables. Don't use the delete the autonumber field method if you have dependent tables because that would require that you also delete relationships. Just empty the table of all rows and compact the database.

 

[emcf]

i reset an autonumber field using the method Pat desribed above (delete all records then compact)....this worked but now whenever i try ro add a new record via a form a warning box appears and says 'record cannot be updated'. once I click on OK then I can update the record freely. how can i get rid of this warning box? it is a minor point but it is irritating me!

 

[emcf]

the problem seemed to be with the form...i created a new form from scratch and the problem was no longer there. anyone got an explanation for this?