[AccessD] Autonumber problem

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