Autonumber after huge record deletion

  • Thread starter Thread starter Andrew Ofthesong
  • Start date Start date
A

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
 
Everything I've read up till now says as long as you have records in the
table the autonumber cant be reset.

Gene
 
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... :(
 
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
 
Thanks!

Kevin K. Sullivan said:
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
Click to expand...
 
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?
Click to expand...

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.
 
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?
 
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?
Click to expand...

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.
 
Back
Top