Problems with Autonumber unique id

[SpiritedAway]

Hi

Having problems with the autonumber unique ID-

When I usually add new records the autonumber is sequential as you would expect. Currently I'm on record 15762 - which is so far, so sequential but this morning I added a new record and autonumber has given it as 1304357 (I don't have that many records in my db)

Is there a reason why my db has jumped to such a high number? And is there anything I can do to get it back to its proper number i.e 15763

I'm using Access 2007

Thanks for the Help

 

[John Big Booty]

The autonumber is only designed to give a unique number, there has been plenty of discussion on the subject of the quirks of the autonumber in this forum in the past, if you search the subject.

If you are looking for a sequential number consider using the Dmax() function. The topic of creating an automatically incrementing sequential numbering using this function has also been discussed at length in this forum.

 

[SpiritedAway]

Still doesn't explain why it's doing that?

 

[John Big Booty]

This post might help explain.

 

[gemma-the-husky]

if you compact the table, the autonumber will reset to the highest number in the table. which might be OK, if you havent used the rogue numbers.

maybe you ran an insert query accidentally with an out of sequence key value - that upsets the autonumber seed

if you set the table for replication (I think - I dont use it) - and I think perhaps this isnt available in A2007 anyway) then the autonumber becomes unique but random

however, in general autonumber generation guarantees uniqueness only, and not sequential numbers. autonumbers should be used only when you need a unique key - if you need to manage the key then you need a different technique

 

[dfenton]

if you set the table for replication (I think - I dont use it) - and I think perhaps this isnt available in A2007 anyway) then the autonumber becomes unique but random

Yes, replicating a table changes the default on an Autonumber from increment to random.

Replication is perfectly available in A2007 as long as you use the MDB format. It is only the ACCDB format that has been crippled by removing this functionality.

In A2007, Sharepoint was promoted as a replacement for Jet Replication, but it was a very poor substitute because of the lack of support in Sharepoint for basic things like referential integrity. Much of that inadequacy will be rectified in the next release of Access/Sharepoint, though there's still a lack of compound indexes (which is problematic for common scenarios like a N:N join table).