Autonumber

[Rolly189]

Hi All - some help if possible

Using Access 2007

I have a table where the RecordID is an autonumber, and PK.

To set this table up I have a load routine, (becuase I have done this multiple times for testing and further deveopment and I compact and repair the DB prior to loading) - the 690 records are correct numerically and autonumbered.

This table has transactional data where existing records are updated and new records are appended.

I found the autonumber was not incrementally increasing - so no reseed the auto number (using ALTER TABLE 3IWSPLans ALTER COLUMN RecordID COUNTER (1000,1)) prior to starting the transactions.

New transactions will append correctly, up untill I amend a existing record (using a delete and append query via a staging table) - then the autonumber tries to reuse a number already in the table and because its not unique it errors.

Can anyone tell me why the autonumber is not incrementally increasing correctly

Thanks
Rolly

 

[MarkK]

I would never ALTER COLUMN on a primary key. There is no reason to reseed the AutoNumber. If you wish to have a meaningful key of some sort, maintain that in addition to your unique autonumber primary key, but don't try to force the autonumber to conform to any pattern.

 

[Rolly189]

Thanks Mark - only tried that to fix the problem (so will cease!). But the problem still exists - the autonumber will correctly append new records but after a update is done it will try and reallocate a used number (usually aroung 336)

 

[MarkK]

Can you post the DB? I have never seen that before and I'd love to troubleshoot it.

Maybe first confirm that the field in question is an AutoNumber, and that it has an index on it marked as both primary AND unique.

thanks,

 

[Rolly189]

Mark

Unfortunately I am unable to post the current DB - I will try and copy (change the names etc) to a new database - and hopefully this issue will keep occuring. Happy to share that with you.

RecordID Data Type - AutoNumber, Field Size - Long Interger, New Values Increment, Indexed - Yes (No Dups)

I have deleted, Compact & Repair (to reset Autonumbers), reloaded - with reseeding.

From further testing I suspect the issue lies with my update process.

New rows are appended as RecordID 691,692, 693 etc.

The test record I have been updating is row 336. The record is put in a temp table, amended, the orginal record deleted from the main table and the new record append with the same RecordID (336) - which accepted as it is unique. For some reason the next insert is also RecordID 336 - not 694 as expected

On the table if I manually insert a row after reloading The RecordID is correct, i.e. 692 etc. After the update if I manually add a record it is the same number as the (last) updated record (eg 336) - not 693 etc as expected.

Hope that makes sense

 

[vbaInet]

Have a look at the following link:

http://allenbrowne.com/ser-40.html

 

[Rolly189]

thanks - will check my our IT guys and ensure SP's are up to date

 

[vbaInet]

I would advise that you also follow the steps mentioned and in order.