autonumber

[Bobcat]

why would the autonumber all of a suden change from a 4 digit number to an 8
digit number?

example;
9400
9401
9402
34286622
34296623
Etc

 

[Allen Browne]

Lots of possible reasons.

A simple one might be because you ran an Append query that specifically
inserted the value 34286622 into the Autonumber field. Once that happens,
Access will continue numbering from there.

It probably doesn't really matter, but if you did want to drop the number
down again, you could delete those records and compact the database.

 

[Bobcat]

it is a table inside a TroubleTicket Database. When there was only 4 I did
try deleting and compacting but after that the next avail autonumber was
always 8digits.

Thx

 

[Tony Toews [MVP]]

It probably doesn't really matter, but if you did want to drop the number
down again, you could delete those records and compact the database.

Trouble is he'd have to delete all the records to reset the seed back
to 0. Jet 4.0 doesn't reset the seed to the highest used value like
Jet 3.5 did.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Allen Browne]

Did you try compacting the TroubleTicket database?

Tony's recollection is that Access won't do that for you, so you would need
to use the code approach.

 

[Allen Browne]

Tony, I thought MS had fixed that problem with JET 4 in a service pack? I
could be wrong about that.

In any case, the code will do it.

 

[Tony Toews [MVP]]

Tony, I thought MS had fixed that problem with JET 4 in a service pack? I
could be wrong about that.

I just tested it in A2000, A2003 and A2007.

In any case, the code will do it.

Yup, but neither of us posted the link to your code.
Fixing AutoNumbers when Access assigns negatives or duplicates
http://allenbrowne.com/ser-40.html

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

Fixing AutoNumbers when Access assigns negatives or duplicates
http://allenbrowne.com/ser-40.html

To Allen:

It would be better if you explained that Jet Replication *requires*
random Autonumbers and that you can't change it back to increment
without ruining your replica set. Indeed, Access prevents you from
ever changing a random Autonumber back to increment, for very good
reasons, because it's likely the highest value (which would have to
be the basis for the new seed) would be very high, and leave very
little room for new entries.

The only way to get rid of a random Autonumber is to create a new
Autonumber field. The easiest way to do that is actually to create a
new empty table and appending the old data, especially if you want
to maintain the original Autonumber values.

 

[Îâ]

ͬ־ÃǺã¡

Did you try compacting the TroubleTicket database?

Tony's recollection is that Access won't do that for you, so you would need
to use the code approach.

 

[Allen Browne]

Okay, David. Have just added a note to acknowledge that switching to
incremental is not applicable to replicated databases.

 

[Tony Toews [MVP]]

The only way to get rid of a random Autonumber is to create a new
Autonumber field. The easiest way to do that is actually to create a
new empty table and appending the old data, especially if you want
to maintain the original Autonumber values.

And which requires a lot of work with child tables such as updating
the keys and relationships and such. <shudder>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/