Auto adjusting the AutoNumber?

[Arvin Meyer]

OK, lets say that union members were added as they became employed, and that
we stored their employment date (something normally done, right?) Wouldn't
sorting on their hire date also put them in seniority sequence?
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Arvin Meyer]

You most likely used DMax(). It is not insurmountable to renumber the
sequence, but it is incorrect to do it for purposes of data maintenance.

I have several resequencing routines that I have written for the purpose of
ordering items on proposals, or in one case ordering stops on a delivery
route. Keep in mind, I use them for resequencing only, not for maintaining
an unbroken set of numbers (although, because of the nature of the way it
works, that is exactly what happens). In all cases, my code just keeps
incrementing the autonumber to even higher values, so 1-20 becomes 21-40,
then when sequence again 41-60.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Larry Linson]

Yep, Access 2002 SP 2 works for me exactly as you describe. I'm glad I saw
this discussion, because I'd have given the same advice about compacting
after deleting at the end!

BTW, Access 2003 works that way, too... no recovery of deleted autonumbers
at the end of the table.

I suppose that is "working as designed and as it should have always worked".
What do you think?

Larry Linson
Microsoft Access MVP

 

[Arvin Meyer]

Yep, Access 2002 SP 2 works for me exactly as you describe. I'm glad I saw
this discussion, because I'd have given the same advice about compacting
after deleting at the end!

BTW, Access 2003 works that way, too... no recovery of deleted autonumbers
at the end of the table.

I suppose that is "working as designed and as it should have always worked".
What do you think?

Larry Linson
Microsoft Access MVP

I just noticed the same thing. I never cared about re-using autonumbers, so
I hadn't noticed it before.

For those who really care. If there isn't a relationship to another table,
you can just delete and re-create the autonumber column. If there are
relationships and the database isn't too big, you can probably convert it
back to Access 97, delete the data at the end, compact a few times and
convert back to the later format. Might just help clean out any old unicode
junk that is laying around too.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Larry Linson]

If there are relationships and the
database isn't too big, you can probably
convert it back to Access 97, delete the
data at the end, compact a few times and
convert back to the later format. Might just
help clean out any old unicode junk that
is laying around too.

To paraphrase: They can have my Access 97 when they pry it out of my cold,
dead fingers. (OR when I get a new machine with a future version of Windows
preloaded that breaks it.)

I learned 'way, 'way back (Access 1.1, maybe, if not 1.0) that AutoNumbers
aren't for anything but uniqueness, so it's not a personal issue. But, I
have always found it convenient that it automatically did that when I
cleaned a datatables-database before releasing to production.

Happy Holidays, Arvin.

Larry Linson
Microsoft Access MVP

 

[Steve Schapel]

Larry,

As I mentioned before, compacting will "reset" the autonumber seed to
1 for an empty table. It is only for tables that have data in them
that the behaviour has changed.

- Steve Schapel, Microsoft Access MVP