How to fix table with autonumber

[DevourU]

I have a repaired table that had a primary autonumber field, and is now a
number. Problem is that there are gaps in the autonumber sequence, so
appending them to a table with a starting autonumber will not work. I need
autonumber to continue again whre it left off. Help? TIA!

-JS

 

[KARL DEWEY]

I read a lot of post that say to not rely on autonumbering if you really want
sequenital numbers because as soon as a new record is create the next number
is used even if the record is not saved. Or any of a number of other
reasons. You really should not use autonumber for anything that a out of
order number would confuse someone. Expect it to happen again. Search on
comments about why you should not use the autonumber for anything except a
reference.

You can append your old autonumbers into the field of a table with
autonumber if it has no records with the same number.

 

[DevourU]

Thankx for the reply, and I understand the autonumber issues. I need to
retain the same numbers in an auto number field. I can start it from the
number I choose.

-JS

 

[KARL DEWEY]

I said that you can append the number-integer that was your autonumber into
the field that is the autonumber of the new table. You have to create a new
table with all the required fields including the autonumber field.

If this does not help then please further explain the problem.

 

[DevourU]

I have a blank structure only table with ECOID data type autonumber. the
repaired table with ECOID data type as number long-integer imports the
records, but creates a new autonumber sequence. TIA

-JS

 

[KARL DEWEY]

You have to have a complete table structure. Open the old table in design
view and Save As a new name. Change the number field to autonumber and save.
Append old table to the new table - the number in the old table will fill in
the autonumber field of the new table with the existing number.
Verify data. Rename old table for safe keeping. Rename new table to that of
old table.
Rebuild any relationships.

 

[Tim Ferguson]

I have a repaired table that had a primary autonumber field, and is
now a number. Problem is that there are gaps in the autonumber
sequence, so appending them to a table with a starting autonumber will
not work. I need autonumber to continue again whre it left off.

Just append the records, it'll all be fine.

If you don't include the (old) autonumber column in the append, then Access
will allocate new ones and they will not match.

If you do include the (old) autonumber column in the append. then Access
will use those values and they will match and the next autonumber will be
the first unused one above the highest-so-far (if you see what I mean).

It is perfectly alright to INSERT a new record with an autonumber; but you
can't UPDATE it afterwards.

Hope that helps


Tim F

 

[DevourU]

I appreciate your patience, but when I append to the new table, the
autonumber field does not fill in from the old table. I starts a new
sequence. The old autonumbers are not added.

-JS

 

[KARL DEWEY]

See Tim Ferguson's post.

I appreciate your patience, but when I append to the new table, the
autonumber field does not fill in from the old table. I starts a new
sequence. The old autonumbers are not added.

-JS

 

[DevourU]

I used a query to append, and it is all good. I was paste appending.

-JS