By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,695 Members | 1,638 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,695 IT Pros & Developers. It's quick & easy.

Auto number

100+
P: 121
Hi
i have one field whose datat type is auto number.Now i entered 10 records.
now when i delete 10 records now the table is empty.when i again enter 1 record autonumber should start from 1.can it be possible
Feb 27 '08 #1
Share this Question
Share on Google+
14 Replies


Delerna
Expert 100+
P: 1,134
The only way I have found to do it in Access is to
1) copy the table
2) paste the table structure only
3) delete the orriginal
4) rename the copy to the name the orriginal had
Feb 27 '08 #2

FishVal
Expert 2.5K+
P: 2,653
Hi.

Compact/Repair will reset Autonumber to the lowest possible.
If you use Autonumber as primary key (which is basically something autonumbers are suited for), then any tricks with transferring data to a new table to "get rid of numbering gaps" are expected to cause broken key relationships.

Kind regards,
Fish
Feb 27 '08 #3

NeoPa
Expert Mod 15k+
P: 31,769
Beware, AutoNumber values are not designed to give a contiguous list of numbers, nor necessarily to start from 1.
Feb 28 '08 #4

Delerna
Expert 100+
P: 1,134
correct, well sort of.
If you set 'New Values' to random in the tables design for that field then they might not start at 1 and they are not contiguous.
When 'new values' is left at the default value of 'increment' however, then they do start at 1 and they are contiguous.
This is a 'one off' use however. Once used the number will never be used again, even if 1 or more, or even all records get deleted. The number sequence just continues on as though nothing has happened

There must be some sort of next number counter for the table in the background and whenever a new record is added it has that number and the next number counter gets incremented.
You need to use tricks as described above to reset it, which is only practical during the development stage of the database. Once the database has been in use in production, then it would probably be unwise to reset it, for the reasons stated by FishVal
Feb 28 '08 #5

NeoPa
Expert Mod 15k+
P: 31,769
Delerna, you might want to reread my post. I didn't say they never WOULD be either. They were never DESIGNED to be either. That is 100% true unless you know something I don't.
Feb 28 '08 #6

missinglinq
Expert 2.5K+
P: 3,532
I think John W. Vinson, MVP, gave the best response I've seen to this type of question:

“When using Autonumber, do be aware that there will be gaps in the numbering - any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and particularly not for consumption by accountants and auditors. Invoice
sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such people get very nervous.”


In short, if you're worried about gaps or about resetting your Autonumber back to zero, you're attempting to use Autonumbers for something they're not intended to be used for, and you need to create a field and use an auto-incrementing numbers scheme!

Linq ;0)>
Feb 29 '08 #7

Delerna
Expert 100+
P: 1,134
unless you know something I don't.
Probably not, I have no idea of what the design motives behind the auto number field was. My appologies NeoPa I did not mean to cause offense. I was trying to put forth things as I see them. Was what I said untrue?
Missingling highlighted well, all the things that can go wrong with the sequence and for those reasons I try not to use them. But.....
Feb 29 '08 #8

FishVal
Expert 2.5K+
P: 2,653
Hi, everyone.

Autonumber in my somewhat speculative opinion is suited to serve as reliable generator of unique values which are mentioned to be used as primary key values. From that point of view it doesn't seem to be an overkill not to reuse a one even if a new record was canceled. Really, in multiuser environment one user may create a new record after an other one has created new record too and before he/she cancels it. To say nothing about deletion of existing records.

Those who do care "numbering gaps" should certainly look for another solutions. I guess everybody knows which. LOL.
Though using those values as PKs is potentially unsafe unless a special measures are taken.

Regards,
Fish.

P.S. Does anybody want to write down an article about this?
Feb 29 '08 #9

NeoPa
Expert Mod 15k+
P: 31,769
Probably not, I have no idea of what the design motives behind the auto number field was. My appologies NeoPa I did not mean to cause offense. I was trying to put forth things as I see them. Was what I said untrue?
Missingling highlighted well, all the things that can go wrong with the sequence and for those reasons I try not to use them. But.....
Delerna, please understand there was no offense taken.
I understood there was none intended and no apology is necessary :)

I posted because I felt you may have misunderstood somewhat, the point I was trying to make. It's a very important one and it comes up very frequently in the forum.

I wanted to make it clear that I stand by what I said 100% and I don't want anyone reading this and thinking there's any doubt about the issue. It causes so many people so much confusion you see, mainly because they're confused about what it's there for.

If anyone wants an insight into what it does and how/why it works the way it does, then Linq's post makes that clearer. Otherwise, it's important to understand what it's NOT there for.
That is 100% true unless you know something I don't.
PS. This line was me covering by rear rather than trying to sound belligerent.
Feb 29 '08 #10

daniel aristidou
100+
P: 491
Just a question but i believe it is possible to change all the pk and then the fk
would just need probably long time to carry out the procedure and careful planning out of code to make sure no errors occur? Though i cant see any point in this laborious chore
Feb 29 '08 #11

NeoPa
Expert Mod 15k+
P: 31,769
If you set referential integrity on linked tables then Access will manage all that for you automatically :)
Feb 29 '08 #12

daniel aristidou
100+
P: 491
If you set referential integrity on linked tables then Access will manage all that for you automatically :)
Ohhh..yeah forgot.
Thanks
Feb 29 '08 #13

P: 3
Hi
i have one field whose datat type is auto number.Now i entered 10 records.
now when i delete 10 records now the table is empty.when i again enter 1 record autonumber should start from 1.can it be possible
0) Insert atleast one record then goto table Design View
1) Delete AutoNumber Field
2) Click on Save but not close the table
3) Now create Field and give the autonumber field then
save now show datasheet view
Feb 29 '08 #14

P: 26
DLN
Hi
i have one field whose datat type is auto number.Now i entered 10 records.
now when i delete 10 records now the table is empty.when i again enter 1 record autonumber should start from 1.can it be possible
I just did something like this I did a Do While .EOF = False loop to delete the files in the table then I did a MoveFirst to go to the BOF then when I wrote the first record to the table I did a !MyAutoField = 0.

The table is not related to any other tables and the field is not the Primary Key so there wasn't any problems and it works great. Everytime I have to delete the table it restarts with 0
Mar 5 '08 #15

Post your reply

Sign in to post your reply or Sign up for a free account.