[AccessD] Autonumber problem

If you do an insert query with the number you want for 'next' in the
query, you can usually shock these back to behaving.  

INSERT INTO MyTable
                         (MyAutonumberField, MyPretendField1,
MyPretendField2)
VALUES        (301, 'Fred', 'Smith')



No guarantees that it won't wander off again though.

Thanks, 


Liz 


Liz Doering 
elizabeth.j.doering at wellsfargo.com 
612.667.2447 


This message may contain confidential and/or privileged information. If
you are not the addressee or authorized to receive this for the
addressee, you must not use, copy, disclose, or take any action based on
this message or any information herein. If you have received this
message in error, please advise the sender immediately by reply e-mail
and delete this message. Thank you for your cooperation.


-----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 3: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