Very high generated IDs

[genefalk]

I have received an Access database from others; when I add a record, it adds the record with an ID in the 10,000,000 range. [FONT=&quot]The highest current ID is about 2100. Any ideas?

Thank you


[/FONT]

 

[Mr. B]

Do you mean that the current record count is about 2100? If the ID field is an autonumber type field then there has been a lot of deleteing of existing records. A number is never repeated when you have an AutoNumber type field.

What are you concerned about?

 

[Zigzag]

Have you tried to compact and repair the DB?

If the highest current ID (autonumber) is about 2100 it should continure 2101.

 

[dcb]

Have you tried to compact and repair the DB?

If the highest current ID (autonumber) is about 2100 it should continure 2101.

Unless a whole bunch of deletes have taken place....

 

[dcb]

If you want a fix try this SQL
Rename "Table1" to your TableName
Rename "ID" to the offending ID
Make sure "2100" is higher than your latest ID otherwise you are going to create duplicate ID's

ALTER TABLE Table1 ALTER ID AUTOINCREMENT (2100,1);

 

[dcb]

Oh
please backup first!!!!!:d

 

[Zigzag]

After a compact and repair the auto number should reset to Max ID + 1, even after many deletes have taken place.

 

[Atomic Shrimp]

None of it this should really matter though - because IDs only need to be unique - they shouldn't really be used in contexts where it upsets anything if this happens.

 

[dcb]

None of it this should really matter though - because IDs only need to be unique - they shouldn't really be used in contexts where it upsets anything if this happens.

Very True - Only if someone has referenced it as a integer in their code somewhere (yes I have done this to myself before )

 

[Zigzag]

they shouldn't really be used in contexts where it upsets anything if this happens.

True, but a lot of people (Not myself) tend to use the autonumber ID as a PR number or Job ID number.

 

[Rabbie]

Remember that Autonumbers can be in either icrement mode or in Random Mode. Random mode will use a random number that can be very large. This mode is used in replicated databases but can be used anywhere

 

[boblarson]

True, but a lot of people (Not myself) tend to use the autonumber ID as a PR number or Job ID number.

If they do, it is not good and it is "use at your own risk" because autonumbers ONLY guarantee you a UNIQUE number, not consecutive, not positive, and not incremented. You can select increment in the properties, but that does not guarantee that it will do so. Autonumbers should ONLY really be used as surrogate keys. Any other use is dangerous. If you need a specific numbering sequence, then you have to program that yourself.

 

[LPurvis]

It's funny how themes come around in cycles.
This will have been mentioned before (even just by me for example) - however...

Autonumbers aren't necessarily consecutive (as mentioned) nor are they unique or repositioning.
i.e. The values by an autonumber will generally be unique (because they're designed to increment). Only the very common primary key on that field forces them to be unique.
Also a compact won't reposition an autonumber's seed back to the Max ID + 1 value.
That was the case for Jet versions prior to 4 and a bug in Jet 4 SP 5/6 saw that behaviour reappear briefly (it was about the time of Access 2002's release IIRC).
Similarly a bug around there saw values reused (which is possible - although the PK will prevent it and raise an error) having seen the seed set almost randomly.
The higher value needn't have been achieved due to deleted rows. An inserted autonumber value of the higher value would have set the seed to that point.

The method suggested by DCB is appropriate to get back to where you want - you can also specify the seed value using ADOX.

As has also been said though, it's unusual for you to be in a position where you should care what the autonumber value is.

Cheers.

 

[ByteMyzer]

After a compact and repair the auto number should reset to Max ID + 1, even after many deletes have taken place.

Only if all entries have been deleted from the table. If any records remain in the table, the AutoNumber will not be reset.

 

[Atomic Shrimp]

True, but a lot of people (Not myself) tend to use the autonumber ID as a PR number or Job ID number.

Yeah, but this is one of the reasons why they shouldn't.

 

[Zigzag]

Also a compact won't reposition an autonumber's seed back to the Max ID + 1 value.
That was the case for Jet versions prior to 4

Not having tried this for a very long time I have just tried it now and Leigh is absolutely correct it doesnt work in the current Jet version. Like I said I dont have the need to reset it as I never use it for anything in context.