Autonumber Tricks?

I apologize if this has been covered, I couldn't find it when I searched.

Access 2003:

I am building a new access database from the remains of an older database that was not Microsoft based. For audit purposes, I need to maintain 2 years of back history data from the previous database. After getting an Excel dump, arranging tables, etc I encountered a problem.

My primary key for the main table is a number, starting at 5000+ and ranging to 6000+ for current data, with gaps in between. I want this primary key to be set to autonumber starting from the last entry (which happens to be 6420). I know there is a way similar to setting the start value for autonumber via append query that will 'trick' access into changing the field settings from number to autonumber despite the table being populated, but I can't for the life of me remember how to do it. Any help would be appreciated.

Copy the structure of your table and change the autonumber to a number and insert one record with the ID 1 less than the number you want to start with.
Then copy paste (or use SQL) this record into your main table then delete it.
Auto numbering will then continue from the ID of the inserted row.

I'm not sure I phrased my question well...


I have a series of numbers that are the primary key of a table.

Numbers range (semi incrementally) from 5000ish on up. The numbers are NOT currently an autonumber field, but I would like them to be.

i.e.


5455
5456
5457
>> There are occasional gaps in the data
5460
5461
...
...
6419
6420
>> I want auto numbering to start here, in this same field.

The table is completely populated.
How do I accomplish this?

Copy the structure as Dennisk says, with no data. Change the ID field to autonumber. Append the data to the new table. The next record will autonumber after the largest value appended (probably, since autonumbers are not guaranteed to be contiguous or even consecutive, just unique).

Worked, after a little tweaking. I got screwed up because when I grabbed tables for the append query something was quirky and it was trying to append blank rows.

Thanks to both of you for the help