reseting autonumber field

[Boris Condarco]

Hi gurus,

Does anyone know how to reset the value of an autonumeric field without
deleting the table? something like the "dbcc checkident" sqlserver command?

 

[Arvin Meyer]

Hi gurus,

Does anyone know how to reset the value of an autonumeric field without
deleting the table? something like the "dbcc checkident" sqlserver

command?

If there is data that isn't related to any other tables' data, you can
delete the column and add it back. If there is no data, or you can delete
all the data, simply compact the database and the Autonumber will reset.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Natasha]

I trancated table, tried to delete column with
autonumber, got error message. I work with Access2000.
Sql statement
ALTER TABLE Employees ADD COLUMN RecordID AUTONUMBER

does not work for me also, please help.

 

[Allen Browne]

To reset the autonumber to the next available value, just compact the
database:
Tools | Database Utilities

You can also reset it programmatically if you have a reason to. With the
ADOX library you can set the Seed property of the Column of the Table of the
Catalog.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Does anyone know how to reset the value of an autonumeric field without
deleting the table? something like the "dbcc checkident" sqlserver

command?

 

[gchamp]

resetting autonumber fiels in Access

Hi gurus,

Does anyone know how to reset the value of an autonumeric field without
deleting the table? something like the "dbcc checkident" sqlserver command?

Hi Boris,
I see a lot of people saying you just need to do a compact and repair to reset autonumber fields .. actually this only works if the table is empty.
The quickest way that I've found it to simply make a "copy" of the table in question using copy & paste (paste the structure and data when asked). Access will allow you to give the new table a new name or name it the same as the original then it will ask if you want to replace the old table with the new. The next record that you insert will now be consecutive. Its not a fancy solution but it works.