 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / January 2008Re-set autonumber in a table (primary key)
I have built a database and tested it thoroughly. I deleted the test records but now my first record has an automunber or 15 and this is winding me up. Can I re-set the autonumber back to 0? Try a compact and repair on the DB (front- or back-end) having that table. > I have built a database and tested it thoroughly. I deleted the test records > but now my first record has an automunber or 15 and this is winding me up. > Can I re-set the autonumber back to 0? Delete the field. Save the table. Insert a new autonumber field  SignatureWayne Manchester, England. > I have built a database and tested it thoroughly. I deleted the test records > but now my first record has an automunber or 15 and this is winding me up. > Can I re-set the autonumber back to 0? > Delete the field. Save the table. Insert a new autonumber field ...or you can simply execute something like this: DoCmd.RunSQL "alter table Table1 alter column ID counter(1,1)" For example counter(3, 10) means: 3 - initial value of counter 10 - step value. K.P. MVP, Poland www.access.vis.pl Why not not just Delete the field. Save the table. Insert a new autonumber field Much better for someone who has no records in a table that has just been created. Mind you I never really bother what an autonumber field has in it. As long as it's unique - thas all I'm bothered about. You could also compact the DB You could also ....etc etc But still think it's better when 1st createing a table to ... Delete the field. Save the table. Insert a new autonumber field SignatureWayne Manchester, England. > > Delete the field. Save the table. Insert a new autonumber field > [quoted text clipped - 7 lines] > K.P. MVP, Poland > www.access.vis.pl (...) > Why not not just > Delete the field. Save the table. Insert a new autonumber field [quoted text clipped - 11 lines] > But still think it's better when 1st createing a table to ... > Delete the field. Save the table. Insert a new autonumber field Your advice is good and I agree with this, what you wrote. Although, what it is better, it depends on what You need at that moment. The "alter table... " gives the easy possibility of steering the counter with code. So it is less well-known than compact or recreate the autonumber field. Therefore I mentioned about it. Kris, Poland www.access.vis.pl Hi Krzysztof You are correct if there are relationships. You method would be better as mine would cause problems. I assumed it was a new stand alone table. SignatureWayne Manchester, England. > (...) > > Why not not just [quoted text clipped - 21 lines] > Kris, Poland > www.access.vis.pl Wayne, I prefer the advice given by Bob, to simply Compact the database. Your idea is more difficult, but still fine, but could get further complicated if this table has Relationships defined based on this Autonumber field, etc. SignatureSteve Schapel, Microsoft Access MVP > Why not not just > Delete the field. Save the table. Insert a new autonumber field [quoted text clipped - 10 lines] > But still think it's better when 1st createing a table to ... > Delete the field. Save the table. Insert a new autonumber field Hi Steve Yes you are right. I assumed it was a stand along table. - Is it too late to make a new year's resolution - don't make assumptions. :-) SignatureWayne Manchester, England. > Wayne, > [quoted text clipped - 17 lines] > > But still think it's better when 1st createing a table to ... > > Delete the field. Save the table. Insert a new autonumber field Hi Wayne, On the other hand, I am only right if one makes the assumption ;-) that they are using Access 2003 or earlier. It appears that compacting the database does not re-set the Autonumber seed in Access 2007! SignatureSteve Schapel, Microsoft Access MVP > Hi Steve > > Yes you are right. I assumed it was a stand along table. - Is it too late > to make a new year's resolution - don't make assumptions. :-) Oooopos!! Correction - sorry!! That is not correct. I was testing on a remote server, which was somehow messing things up. When doing it locally, it behaves as in earlier versions - I should have paused before posting! SignatureSteve Schapel, Microsoft Access MVP > Hi Wayne, > > On the other hand, I am only right if one makes the assumption ;-) that > they are using Access 2003 or earlier. It appears that compacting the > database does not re-set the Autonumber seed in Access 2007! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.