Autonums - corrupt/very high

[NJS]

1. Does anyone know why/how an autonum field in a table in Access (2000) can
jump from 837 to 258978675? If I do an (SQL)insert, then a DMAX, I get
258978675 (or whatever is the next (high) #).
If I do a manual insert, then 838 is allocated as the autonum field. On a
different platform the next number is always 258978675, regardless of the
insert method?
It appears the autonumbering mechanism is running two sequences?
2. Is there any way of replicating the table, and ensuring the next number
is 838. The copy process seems to inherit the high sequence. (I tried the
append query and it didn't work)
thanks very much - in advance.

 

[LeAnne]

Hi NJS,

Autonumbers aren't meant to be sequential, just unique. If you insist on
trying to get them to conform to some human-meaningful sequence, you're
setting yourself up for some major headaches down the road...should you
delete a record, or replicate the database, the autonumbers will go out
of whack. Having said that, you can either delete all records with the
weird autonumbers, compact the db, then re-append the records; or change
the autonumber field to a Number (Integer) field, use an update query to
renumber the records; then change the field back to an autonumber (if
it's the table's pk, you'll have to turn that "off" before trying this).
And of course, related records in other tables will have to be changed, too.

Good luck,

LeAnne

 

[NJS]

Thanks Leanne,
Unfortunately Autonumbers ARE sequential (unless specified random or sync).
Once one has populated a (long) field it is not possible to change it to
autonum.
I am unable to delete the 'weird' fields - error = "Key not found"
The update method does not work/help - there are two numbering sequences in
effect: one from 838 upwards and one from 258978675.
Even if it was permissable, I cannot copy the existing data to a new table
and then set the column to autonum because there are missing numbers.
Norman.

 

[LeAnne]

Thanks Leanne,
Unfortunately Autonumbers ARE sequential (unless specified random or sync).

Sorry, this is incorrect. Autonumbers will ALWAYS have gaps. They may
START sequentially when you first create them. But if you were to delete
any record (say, #458), then the numbers would no longer be sequential.
Access will not renumber the remaining records from 459 onward to
maintain the sequence (nor should it, as it would then have to
perpetuate the change in all related records). The same goes if more
than one record is deleted. And should you delete a large chunk of
records from a table and/or append new records without first doing a
Compact/Repair, Access will not necessarily begin the next new record
with the next number in sequence. Again, Autonumbers are NOT meant to be
sequential. Access doesn't care if one record is number 28, and the next
one is -72.5643210E3 (which can happen if you replicate!)...they're
still uniquely identified, and that's all that matters to Access.

Once one has populated a (long) field it is not possible to change it to
autonum.

Whoops! You're correct. You can change an autonumber to Long Int, but
not the other way around.

I am unable to delete the 'weird' fields - error = "Key not found"

Huh? Just open the table in Datasheet View, highlight the rows you want
to delete, then click the Delete toolbar button (red "X"). Again, you
MUST Compact/Repair before attempting to re-append the records.

The update method does not work/help - there are two numbering sequences in
effect: one from 838 upwards and one from 258978675.

You lost me here.

Even if it was permissable, I cannot copy the existing data to a new table
and then set the column to autonum because there are missing numbers

See above.

LeAnne

 

[NJS]

Leanne,
By default Autonums ARE issued sequentially('Increment'), unless you specify
'random' or are synchronsing.
I know that you can delete records, leaving gaps. Access WILL begin the next
new record with the next (unused) number in sequence (unless you have deleted
some).

My problems are much more complex than where we are dwelling. I need to know
how to recreate the table, with the next autonum immediately after 837 (It
now issues 258978675 because it is inheriting the high sequence). The append
method does not work in this case.

 

[Graeme Richardson]

Hi, I've read the previous thread on your message.

You need to reset the count, To do that you have to recreate the ID field
which can't be done in a table that has data, so we recreate the entire
table.

0) Compact and repair the database.
1) Copy and paste the TableOld (with a different name, TableNew) and choose
structure only when prompted. Check that the ID on the new table is
Increment and not Random. The AutoNumber is reset to 1
2) Run an append query from TableOld to TableNew, excluding the record with
ID 258978675
3) Append the record with ID 258978675, changing it's ID to 837
4) Drop TableOld and rename TableNew to TableOld.
5) Update fields in related tables that reference ID 258978675 to 837
6) Reset relationships.

HTH, Graeme.

 

[NJS]

Thanks Graeme & Leanne.
This worked great (I will have to manually enter the 'high' data (due to
autonum) but that is no problem.
I believe my prior attempts did not work because I copied data as well, not
just structure.
Do you have any idea what causes this state?
cheers.
Norman.

 

[John Vinson]

Do you have any idea what causes this state?

An Append query will very often leave a gap. I *speculate*, without
much evidence, that Access estimates the number of records which will
be appended, ignoring any criteria on the append query; sets the "next
autonumber" value accordingly; and appends the records which actually
meet the criteria. If Access guesses you'll be appending 50250 records
and you actually append only 50, you'll get a 50200 number gap.

John W. Vinson[MVP]

 

[NJS]

Sounds feasible, especailly with multi-user and possible bad comms. - very odd.