Autonumber as Primary Key, renumbering?

[Crookshanks]

Hello,

In my application I use a lot of tables which have as primary key an integer value which is created with the autonumber fearture. After a lot of testing, deleting, creating I have a lot of caps in the numbering....

Like 1,2,3,4,9,13,19,20,25,100,101 etc. etc.
Of course the primary key is linked to other tables with several relationships.

Question: is it possible is Access to renumber the primary key to consecutive numbers and correct / maintain alle the relationships as well? I guess not, buy maybe someone has a good idea?

Kinds regards,

 

[PHV]

And why would you do this sort of renumbering ?
What is a matter ?
An autonumber shouldn't have other special meaning than being unique.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[2drunk2funk]

Autonumber does not give you consecutive numbering (unfortunately). Well it does, but if you cancel a new record creation you lose the number it used, hence the gaps.

To maintain an unbroken sequence you will have to manage your own PrimaryKey number which entails storing the number in a control table and managing the allocation and increments yourself :|

Access does not have a way of renumbering data to remove gaps, that would be fun with all the relations, assuming you access knew about them all.

I remember converting a large multiuser Access97 db to Access 2000 and falling foul of the changes MS made. When you compact and compress the database it was re-using old numbers. Was a nightmare to debug it.

 

[Blorf]

I recently built a project database and ran into the same problem. Here is how I solved it.

I used the autonumber field to generate a number, but did not make it a primary key. Instead on my form, I took the autonumber and forced it into another field (The primary Key) set the relationships accordingly, to this field with cascade updates.

To renumber, I use three simple queries. The follow.

ALTER TABLE projects Drop ID; (Deletes the Autonumber field)

DoCmd.RunSQL "ALTER TABLE Projects ADD COLUMN ID COUNTER(1,1);" Puts the autonumber field back, with new numbers.

And..

UPDATE Projects SET Projects.ProjectID = Left([projectid],Len(Trim([projectid]))-4) & Right('0000' & [id],4); (Fixes my primary key to the new numbers)

Probably not the best way to do things, but it worked for me.



"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[PHV]

Blorf, hopefully no ProjectID foreign key elsewhere in the DB ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Blorf]

No forign key, just using it for relation.

Like I mentioned, it probably isn't the best, or even a good way to do it, and I wouldn't have done it all but after I built the thing, the user "Boss" kept typing one line, saying never mind, and ended up with gaps in the numbers, which of course don't really matter, but he didn't like it, so....

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[PHV]

No forign key, just using it for relation
???
Which kind of relation without FK ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244