How to Reset Autonumber in a table. 4

[CptCrusty1]

I'm doing some testing and, although its not really necessary, I'd like to reset the autonumber back to 1 in a table. I know this is possible, however, my research is apparently suffering from lack of coffee.

Thanks.
Richard.

 

[Remou]

See last post:
Resetting Autonumber
thread702-1122847

 

[PHV]

CurrentDB.Execute "ALTER TABLE yourTable ALTER COLUMN myID COUNTER(1,1)"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JeremyNYC]

Keep in mind, though, that there is no reason to reset the autonumber field. That number should NEVER be relied upon to present any kind of meaningful data. Autonumbers are guaranteed (more or less) to be unique. They are NOT guaranteed to be sequential, so the notion of assigning any significance to them is one that will lead you down a dangerous path. See my write-up here for a bit more detail:

http://www.abcdataworks.com/id33.htm

Jeremy

---
Jeremy Wallace
METRIX Lead Developer
Fund for the City of New York
http:// metrix . fcny . org

 

[LittleSmudge]

The simple way is to delete all the records in the table and then compact the db.


Remember though, that if you have ( As you should ) split the app into FE & BE then you can be in the FE and delete the records. You can then compact the FE as many times as you like and have no effect on the AutoNum. You need to open and compact the BE itself to reset the AutoNum.


'ope-that-'elps.

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[CptCrusty1]

All, thanks for your replies!

PHV. Awesome piece of code.. Thx!
Little Smudge. That works too.
Remou. you too, those SQL snippets will come in handy.

My reason for clearing out the Autonumbers is simply a housecleaning quirk of mine. Before I hand over a finished product, I want it to be clean. Nothing in my code or SQL relies on the autonumber.

Other than that, you all have demonstrated that there are numerous methods, as usual, to accomplish the same thing.

Thanks again.

 

[Remou]

Thank you for the star, but I was merely recommending PHV previous post of similar code to the above, which, as you say, is very nice indeed.

 

[CptCrusty1]

Ah... but the fact of the matter is you know about it whereas I did not. Also PHV recommended a completely different solution. Even though the source was PHV, you knew, I didn't, your credit. I also Star'd PHV for the help as well.

OH.. you're welcome. Just remember me at Christmas.

 

[dwight1]

PHV,

Was reading the posts a small doubt.

Is the code
CurrentDB.Execute "ALTER TABLE yourTable ALTER COLUMN myID COUNTER(1,1)"

EXECUTABLE in query analyser of SQL.

Dwight

 

[PHV]

No, it's VBA code executing JetSQL code.