Table has had autonumbers removed and need to reinstate

[maverickmonster]

Hi All

This is my first post so hope I get everything right

Right i have three tables which are laid out as such

ID Number PK
Capacity_Band Number
Product Number
Price Number

previously the ID was a autonumber but this has been removed recently. When I try to change the ID back to Autonumber Access throws up an error. Some of the ID's occasionally get deleted which results in non consectutive numbers, normally I would strip the Id field and start again , but unfortunatly the id's are all interlinked , so this is not an option , unless i reset them all !!!!

My only thought is to set up a complicated series of lookups (probably vlookups in excel to reset the numbers) . Im hoping there is an easier way to reinstate the Autonumber on the ID field ?

Many Thanks for help in advance


Maverickmonster

 

[joepele]

There were quite a number of posts on this... but being a newbie myself... I'm still trying to sort this out...

But hope this will help you more than it did me.

http://www.access-programmers.co.uk/forums/showthread.php?t=146404&highlight=dmax

More posts on it.
http://www.access-programmers.co.uk/forums/showthread.php?t=147047&highlight=dmax

 

[boblarson]

normally I would strip the Id field and start again , but unfortunatly the id's are all interlinked

Then you are using an Autonumber incorrectly! An Autonumber ONLY guarantees you an UNIQUE number, not consecutive numbers. If you need consecutive numbers then you need to create your own numbering scheme.

Autonumbers have been described best as "Not for human consumption." You should never use an autonumber unless you can live with gaps or numbers that can be negative or jump hundreds of numbers as those are all possible at various times depending on your database use.

 

[maverickmonster]

You should never use an autonumber unless you can live with gaps or numbers . . . . . .

BobLarson

I can live with the numbers it has been given , Thats what I Need , but i need to make the table Autonumber again as it is currently just Number !


edit: got the solution found on a different forum , look at the bottom for the Accepted solution

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22119801.html

 

[boblarson]

Sorry, I was sleeping. Glad you got it sorted.