Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to Reset Autonumber in a table. 4

Status
Not open for further replies.
CptCrusty1

CptCrusty1

Programmer
Feb 17, 2005
216
US
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.
 
Sort by date Sort by votes
See last post:
Resetting Autonumber
thread702-1122847
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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:
Jeremy

---
Jeremy Wallace
METRIX Lead Developer
Fund for the City of New York
http:// metrix . fcny . org
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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. :)
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Zyborg
  • Locked
  • Question
Using a variable as reference to a form
Replies
5
Views
686
Zyborg
Zyborg
btamsgn
  • Locked
  • Question
How to add lines from textbox to table 1
Replies
8
Views
469
MattGreer
MattGreer
breezett93
  • Question
Converting from 32bit to 64 bit. After converting, database takes significantly longer to open 1
Replies
14
Views
1K
dhookom
dhookom
M
  • Locked
  • Question
display a calendar with appointments in body of html email with access vba
Replies
1
Views
3K
SoftwareRT
SoftwareRT
Moss100
  • Locked
  • Question
Vba adding record to table when field is integer
Replies
8
Views
605
Moss100
Moss100

Part and Inventory Search

Sponsor

Back
Top