I would like to renumber my primary key

[Philly44]

Hello all,
I am sure that the answer to my question has been posted before but for some reason I can't find it. I am currently in the process of bringing another group into a central Access97 db that I work on. In order to port there old data over I basicaly made an empty copy of the current data structure and imported the old data in. Now because users woudl be using this db while I was importing I had to start any autonumber fields quite a bit a higher then their value in thw working version. The problem I have run into is I had to clear the main table a couple of times and (I'll admit I forgot to check) now the Primary Key ID is a lot higher then I would like it to be. Is there a function or module that can re-index the db? If all goes well I would like to re-index the main one after I bring the new group online.

Thanx again, Your help is always appreciated
Chris

 

[pcdaveh]

If you have your table empty... Perform a compact and repair on the database. This will reset the Autonumber field...

 

[pcdaveh]

Another database trick along the same line.

http://wa.apana.org.au/~abrowne/ser-26.html

 

[Philly44]

Thanks for the responce but its not that easy. I have more than 10,000 records in there and I cant have the index restart at 0. I am going to be putting this data into the working system very soon and the working system already has more than 50,000 records so I need to be ab;e to specify that I want my index in the temp db to start at 60,000 or something like that.

Thanx

 

[pcdaveh]

Follow the link that I specified next. It addresses that sort of situation. That's why I put it there...

 

[Philly44]

Thanx I'm checkig right now. I didn't see it before I posted I must have been posting at the same time as you

 

[Philly44]

I have checked several links and I haven't really found anything that helps. Any other suggestions would be greatly appreciated.

Thanx
Chris

 

[JimHorton]

Chris,
I haven't seen that link that was referenced above, but what would be the harm in just not using an autonumber at all? My 2c has always been to avoid that field type like it's the plague, which you've found it is. It's quite simple to replicate the functionality of autonumber (I think there are numerous faq' here on just that), and you won't have these issues anymore.
--Jim

 

[hoggbottom59]

If you have the field set to Required 'Yes' and Indexed to 'Yes - No Duplicates' then as longer as the ID is incremented it doesn't need to be an Autonumber type.

You could then use a field in a table to keep track of the next or last number used.

I have a single field in my database that keeps track of a number used for linking records. The table only contains 1 record but other important fields. If this number is placed into the field 'Link-No' of any records then the application displays these records as being associated. The number is incremented and placed back in the field for next time.

Just another suggestion but impractical when records are frequently being added.

Leon.