[AccessD] Autonumber problem

Thanks to all who replied.  I had performed a compact and repair to no
avail.  The suggestion by Jack looks like what I need.

This is Access 2003 SP3 database.  I took another look at it is in 2000
format.  Could this be a potential problem?  Should it be converted to
2003 format?

Does anyone know what series of events causes the problem to occur?

Joe O'Connell

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Monday, May 19, 2008 5:24 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Autonumber problem

This was a known bug back in 2K (I believe) which an SP sorted out.  The

bug "reset" the autonumber to the next "available" number, which of 
course is at the beginning of the first "hole".  I have dealt with this 
MANY times.

If you perform the C&R using a machine where the bug has not been fixed,

then you will CAUSE the problem, not fix it.  To my knowledge the fix 
never "REPAIRED" the problem, i.e. it never resequenced the autonumber 
out past the last used number, a manual fix such as discussed previously

has always been required.


John W. Colby
www.ColbyConsulting.com


Heenan, Lambert wrote:
> I agree with Gary. I've seen this exact problem happen before and
compact
> and repair sorted it out. Any solution involving fiddling around with
the
> 'next' value or redefining the field properties should be a last
resort if
> C&R does not do the job. IMHO of course.
> 
> Lambert
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Joe
O'Connell
> Sent: Monday, May 19, 2008 4:18 PM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Autonumber problem
> 
> There are several tables in an application that have developed "holes"
> in the autonumber field.  This is not a problem, except that Access
has lost
> track of the next autonumber to use so it is reusing the autonumbers
that
> are no longer in the table.  
> 
> For example, the autonumbers may go from 1-100 and then 200-300.
Instead of
> using 301 as the next autonumber, Access uses 101.  There is no
problem
> until the autonumber for the new records reaches 199, then Access
attempts
> to use 200 for the next autonumber.  Since 200 is already in use, the
record
> cannot be created.
> 
> Has anyone else experienced a problem like this?  What is the
recommended
> method to correct this problem?  Is there a way to reset the seed for
the
> autonumber to force it to resume after the largest in the table?
> 
> Joe O'Connell
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com