Reset auto number in Access
-
Hi, I have a access database and my table of access database have a Auto number field. and i insert 50 record in the access data and then i delete the last 25 records in the database. after this i insert new record but auto generated filed show the Id is 51. but i want to change it with 26. and next is autogenerated according to this. how i do this. please suggest. Thanks
~Khatri Mitesh khatrimitesh@hotmail.com Bikaner (Rajasthan) INDIA
-
Hi, I have a access database and my table of access database have a Auto number field. and i insert 50 record in the access data and then i delete the last 25 records in the database. after this i insert new record but auto generated filed show the Id is 51. but i want to change it with 26. and next is autogenerated according to this. how i do this. please suggest. Thanks
~Khatri Mitesh khatrimitesh@hotmail.com Bikaner (Rajasthan) INDIA
As far as I remember you can't (not used Access for nearly 5 years :) ). And why would you want too? Why was it made an Auto number in first place? If there's a good reason then you should not change it. For example, is it a foreign key in some other table? However here's two possible approaches. Use a non Auto number(long?) then before inserting a record do a SELECT to get the max in the field, add 1 to result and insert new record with this as value. You have to do this for every insert. Alternatively, after deleting records, create a another table with same structure, copy records from the table to the new one (but exclude the Auto number field), delete the original table, rename the new one to the proper table name. Now you can insert and Auto number will work. But you would be mad to do this. :)
Regards David R
-
As far as I remember you can't (not used Access for nearly 5 years :) ). And why would you want too? Why was it made an Auto number in first place? If there's a good reason then you should not change it. For example, is it a foreign key in some other table? However here's two possible approaches. Use a non Auto number(long?) then before inserting a record do a SELECT to get the max in the field, add 1 to result and insert new record with this as value. You have to do this for every insert. Alternatively, after deleting records, create a another table with same structure, copy records from the table to the new one (but exclude the Auto number field), delete the original table, rename the new one to the proper table name. Now you can insert and Auto number will work. But you would be mad to do this. :)
Regards David R
Forget it, this is some stupid pratt who has no idea what a primary key is used for and is distressed about the missing numbers
Never underestimate the power of human stupidity RAH
-
Hi, I have a access database and my table of access database have a Auto number field. and i insert 50 record in the access data and then i delete the last 25 records in the database. after this i insert new record but auto generated filed show the Id is 51. but i want to change it with 26. and next is autogenerated according to this. how i do this. please suggest. Thanks
~Khatri Mitesh khatrimitesh@hotmail.com Bikaner (Rajasthan) INDIA
Do NOT frack with the autonumber process, it is used as a primary key, a primary key should have ABSOLUTELY NO intelligent meaning, there should be no relevance whether the number is 26 or 51, it should ONLY be used for maintaining the relational structures. Read some books on data structures and database design. This is Database 101 and should be known by EVERY developer who touches a relational database. Leave the bloody thing alone !slap!
Never underestimate the power of human stupidity RAH
-
Forget it, this is some stupid pratt who has no idea what a primary key is used for and is distressed about the missing numbers
Never underestimate the power of human stupidity RAH
-
Hi, I have a access database and my table of access database have a Auto number field. and i insert 50 record in the access data and then i delete the last 25 records in the database. after this i insert new record but auto generated filed show the Id is 51. but i want to change it with 26. and next is autogenerated according to this. how i do this. please suggest. Thanks
~Khatri Mitesh khatrimitesh@hotmail.com Bikaner (Rajasthan) INDIA
Rather than beat up the poor questioner for asking, I'll just trust that he has his reasons and offer the suggestion that he use tools/database/compact and repair after deleting the records. This will cause the next autonumber to proceed in sequence. In general you should not do this for all the reasons others have supplied. But I have been known to do it myself when cleaning out test data before deployment.