ACCESS FEED: Re: [MS_AccessPros] Auto number mystery

Senin, 27 Januari 2014

Re: [MS_AccessPros] Auto number mystery

Robin-

In the early days of Access, Autonumbers used to get corrupted a lot. They more or less fixed that when they enforced the rule that the "next" number gets used up as soon as you dirty a new record. You can do one of two things:

1) Copy the table to the clipboard, then paste structure only to a new table. Then paste append the records on the clipboard to the new table. Delete the old table and rename the new one.

2) Run this SQL:

ALTER TABLE membership ALTER COLUMN MemberID COUNTER(9371, 1);

The first will definitely fix your problem and remove any corruption. The second simply resets the "next" number but won't necessarily fix any corruption. You can figure out the actual number you need to use by opening the table in Datasheet view and sort descending on MemberID. Add 1 to the highest value you find.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Jan 27, 2014, at 8:25 AM, Duane Hookom <duanehookom@hotmail.com> wrote:

> Robin,
>
> If you have 9370 records, why aren't new autonumber values at least 9370?
>
> Do you know why the record could not be saved? Is there an issue with some enforced integrity?
>
> Duane Hookom MVP
> MS Access
>
> ----------------------------------------
>> From: robinski@mymail.net.au
>>
>> I am using Access 2013 on a Win7 system.
>>
>> I have a membership table with the MemberID field as an auto number.
>> There are 9370 records and today when attempting to add a new record
>> MemberID=5260 was allocated which created a record which could not be saved.
>>
>> A second attempt allocated MemberID=5261 and the third attempt was
>> MemberID=5262.
>>
>> What have I broken?
>>
>> Many thanks,
>>
>> Robin Chapple
>
> ------------------------------------
>
> Yahoo Groups Links
>
>
>
>