[AccessD] More Strange Autonumber Behavior - Access 2002 (aka Access XP)

Interesting idea John. I just tried it out and it works a treat, but the fly
in the ointment is that your stuck with my more roundabout method if you
happen to be using random 'incrementing'. 

As for Jim's comment about "Natural Keys" (yuk!) I think one problem like
this in ten years of Accessing is much less hassle than working with Natural
Keys (yuk!) for the same period of time. :-)

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: Thursday, January 05, 2006 3:32 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] More Strange Autonumber Behavior - Access 2002
(akaAccess XP)


Lambert

I make an append query where I get the max() of the ID field, add 1, alias
it and append that back in to the table as a new record.  This adds a bogus
record to the end of the table which I then delete back out.  The autonumber
is now reset and off I go.  The only issue is if there are other fields that
have constraints then it becomes a bit more difficult discovering and
building such values. 


John W. Colby
www.ColbyConsulting.com