Autonumber Tables - Need ability to reset

[Guest]

Hi,

In Access 2003, I have a meetings table with a Meeting ID as the primary
key. This is directly associated with the Meetings form, so as the user
fills the Meetings form the meetings table gets updated. Now, in addition to
this i have a delete button for this form and have deleted several records.
The meeting ID in initially(before delete feature) had a continuous flow 1,
2, 3, 4, 5, 6, 7, 8, etc. Now, after introducing the delete feature and lets
say i deleted 1, 4, 5, 6, 7, and 8. Now the flow is 2, 3, etc. If I were to
create a new record it will start with 9. Since, I was working on my test
environment and want to roll this out to the production environment, is there
a way to have a one time reset feature where the autonumber starts at 1
again. Thanks in advance.

 

[Rick Brandt]

Hi,

In Access 2003, I have a meetings table with a Meeting ID as the primary
key. This is directly associated with the Meetings form, so as the user
fills the Meetings form the meetings table gets updated. Now, in addition to
this i have a delete button for this form and have deleted several records.
The meeting ID in initially(before delete feature) had a continuous flow 1,
2, 3, 4, 5, 6, 7, 8, etc. Now, after introducing the delete feature and lets
say i deleted 1, 4, 5, 6, 7, and 8. Now the flow is 2, 3, etc. If I were to
create a new record it will start with 9. Since, I was working on my test
environment and want to roll this out to the production environment, is there
a way to have a one time reset feature where the autonumber starts at 1
again. Thanks in advance.

There are numerous *completely normal* activities that will produce gaps in an
AutoNumber sequence (sometimes large ones). If you do not want gaps then you
should not use an AutoNumber.

 

[Guest]

After you delete all the records from the table, then run compact on the MDB
that contain the table, that will reset the counter back to 1

 

[Rob Oldfield]

It will? Even if there are other (child) tables that still have foreign key
data in them? Sounds remarkably dumb to me.

http://support.microsoft.com/default.aspx?scid=kb;en-us;812718

- note the link towards the bottom pointing at...

http://support.microsoft.com/kb/287756/

 

[Rob Oldfield]

For reference, the reason I questioned Ofer's post is that I don't have a
clue how it works. Because I've *never* bothered doing it. Because it
should be completely pointless. AutoNumber values *should* be completely
invisible to users.

 

[Guest]

Thanks Ofer for the quick response. I was trying to further understand what
you meant by "run compact on the MDB that contain teh table". I am little
new to this, so I didn't follow what that means or what steps I need to take
for that. Thanks.

 

[TC]

Rick is right. Your expectation that an automnumber field will stay
continuous (1,2,3..), is wrong.

Open any table that has an autonumber. Try the following sequence of
actions:

- type a character into any of the other fields;
- pess the Esc key a few times;
- repeat the above two steps, a few times;

See what happens to the autonumber!

HTH,
TC

 

[Jeff Boyce]

Access Autonumbers are designed to provide unique row identifiers... no
promises beyond that. The 'group's responses are reminding you that if you
want/need sequential numbers, DON'T plan on getting them from an Autonumber
field. Some (me) would go so far as to suggest that autonumbers are unfit
for human consumption, and should ONLY be used to uniquely identify
(internally) a row in a table.

 

[Rick Brandt]

It will? Even if there are other (child) tables that still have foreign key
data in them? Sounds remarkably dumb to me.

He did say that this would happen only if you have deleted all records in the
table prior to the compact. If you are using referential integrity then that
would rule out the existence of any child records.

 

[Rob Oldfield]

He did say that this would happen only if you have deleted all records in the
table prior to the compact. If you are using referential integrity then that
would rule out the existence of any child records.

True. I wasn't criticising Ofer, just pointing out that I feel that
resetting AutoNumbers during a different (and completely unrelated) task is
going to cause problems for people who a) aren't expecting it (i.e. anyone
expecting the compact to behave in the same way as in earlier versions) and
b) don't have referential integrity switched on.

Why not just pop up a message box saying "The table 'Whatever' has no
records and an AutoNumber field, do you want to reset it?" Or, better,
provide an explicit method of doing the reset?

 

[Rick Brandt]

True. I wasn't criticising Ofer, just pointing out that I feel that
resetting AutoNumbers during a different (and completely unrelated)
task is going to cause problems for people who a) aren't expecting it
(i.e. anyone expecting the compact to behave in the same way as in
earlier versions) and b) don't have referential integrity switched on.

Why not just pop up a message box saying "The table 'Whatever' has no
records and an AutoNumber field, do you want to reset it?" Or,
better, provide an explicit method of doing the reset?

That might be why they changed this behavior in one of the newer versions. AIUI
compacting used to reclaim all used AutoNumbers higher than the maximum value
still in use. The change is that now AutoNumbers are only reclaimed when the
table is completely emptied prior to the compact.