Autonumber after huge record deletion

[Andrew Ofthesong]

Hi, i have an autonumber as ID in a table.

if i have 1000 records, after i delte 100, the next autonumber will be 1100,
not 900, Right?

How can i make it to be 900 (i mean, to be te next secuentian number from
the last record?)

I try to compact the database, but not...

Thanks a lot

 

[Gene]

Everything I've read up till now says as long as you have records in the
table the autonumber cant be reset.

Gene

 

[Andrew Ofthesong]

In fact, if you go to goolge, and enter "reset acces autonumber", there will
appear a lot of sites, and all say that compacting your database will reset
the autonumber tothe next sequential number.

But it doen't happens to me...

 

[Kevin K. Sullivan]

I, too, saw this behavior change. MS explains it in

http://support.microsoft.com/?kbid=287756


I wasn't using ACC2002, I was using ACC2000,
http://support.microsoft.com/default.aspx?scid=kb;en-us;812718 specifies:

You can also reset the AutoNumber field if you compact the database.
However, this may not always work successfully with Access 2002. You may
have more success if you compact the database with Access 2000 and Jet 4.0
version of the Microsoft Jet Database Engine. For additional information,
click the following article number to view the article in the Microsoft
Knowledge Base:
287756 ACC2002: AutoNumber Field Is Not Reset After You Compact a Database

------

Of course, this is only an issue when we abuse autonumbers by letting users
see them. See #7 in
http://www.mvps.org/access/tencommandments.htm

HTH,

Kevin

 

[Andrew Ofthesong]

Thanks!

I, too, saw this behavior change. MS explains it in

http://support.microsoft.com/?kbid=287756


I wasn't using ACC2002, I was using ACC2000,
http://support.microsoft.com/default.aspx?scid=kb;en-us;812718 specifies:

You can also reset the AutoNumber field if you compact the database.
However, this may not always work successfully with Access 2002. You may
have more success if you compact the database with Access 2000 and Jet 4.0
version of the Microsoft Jet Database Engine. For additional information,
click the following article number to view the article in the Microsoft
Knowledge Base:
287756 ACC2002: AutoNumber Field Is Not Reset After You Compact a Database

------

Of course, this is only an issue when we abuse autonumbers by letting users
see them. See #7 in
http://www.mvps.org/access/tencommandments.htm

HTH,

Kevin

 

[John Vinson]

Hi, i have an autonumber as ID in a table.

if i have 1000 records, after i delte 100, the next autonumber will be 1100,
not 900, Right?

Wrong.

The ONLY function of an Autonumber is to provide an almost-guaranteed
unique key. It is NOT guaranteed to be gapless. In fact, if you delete
records, you have no way of knowing that someone hasn't written down
937 on a slip of paper, referring to a deleted record - and if you now
create a new DIFFERENT item 937 they'll possibly get confused.

If you want gapless, sequential numbers, *don't use an Autonumber*;
instead use some VBA code to create a Custom Counter in a Long Integer
field.

 

[Andrew Ofthesong]

Thanks John

Anyway, as one of the golden rules of Access is to use utonumbers internally
only (don't let users to see them), is quite safe to use it as wherever you
want, as long as is consistent.... or not?

 

[John Vinson]

Thanks John

Anyway, as one of the golden rules of Access is to use utonumbers internally
only (don't let users to see them), is quite safe to use it as wherever you
want, as long as is consistent.... or not?

Well, within limitations of course; if you have a good natural primary
key (e.g. the two letter State code for a table of US States and
Canadian provinces), or if you want to enforce some other field or
fields as a Primary Key, then an Autonumber might be unnecessary. But
yes, using an Autonumber as a surrogate key, and linking it to a Long
Integer field in other tables, is something I do all the time. The
point is that no meaning should be assigned to the autonumber field's
value.