Appending Records with a desired auto number in a table with Auto Number

[El hombre Loco]

Here is my issue. In a table with an Auto Number index some records have been deleted. I have been able to recreate them along with their original auto number. The problem is that I do not know how to append these records forcing the original auto number. I have tried changing the auto number field to a number field in the table, this works except I cannot change it back to auto number.

I am sure I’m not the first with this question or issue. I did search through a couple hundred entries about auto number before I posted this question.

Jim

 

[Pat Hartman]

The only way to do it is to run an append query. Hopefully you still have the original version of your table - before you changed the autonumber to a long integer. Restore it. Put the records you want to add in a temp table. Then run an append query that selects the rows in the temp table and appends them to the original table.

 

[El hombre Loco]

Thank you

Pat, I sure thought I tired this (Append) but I obviously had not, as I just tried it and it worked.

I know have done this before and could not remember how I'd done it.

BTW I am not working on the orginal data but copies.

Thank you very much for your help.

 

[Pat Hartman]

BTW I am not working on the orginal data but copies

Good move That lesson comes hard to some people.

 

[ekses90]

hi..can you explained more how to append query and temp table?.i,m having the same problem and i dont know how do it.

 

[ootkhopdi]

hi..can you explained more how to append query and temp table?.i,m having the same problem and i dont know how do it.

IS this problem solved
i m also facing this problem that every time i append data to my table it start with progressive no. not with "01"
pls help me too that how can u solve this problem than every time append data start with auto number from 01

:banghead:

 

[RainLover]

When you append, do not leave the AutoNumber field empty. You need to append the number that you wish to use.

If no number is nominated then the next number in the AutoNumber Sequence will be applied.

Also do not use 01. Instead use 1.

 

[Pat Hartman]

Autonumbers are meaningless except as a way to uniquely identify a record. It doesn't matter whether the number is 12 or 392029348. It still uniquely identifies a single record.

If you have numbers from a different table and you want to append them as autonumbers, you can do that as long as there is no conflict with an existing record.

If you are appending new rows without existing autonumbers so that Access generates new numbers for you, it starts where ever it left off last time. If the last record appended was 19902 then the next will be 19903. Emptying the table will not reset the seed. In some versions of Access emptying the table and then compacting the database will reset the seed but not all versions and the seed won't be reset to anything lower than the value of the highest existing autonumber.