I have sinned! autonumber used as a unique identifier.. now to fix it

[munkee]

Hi all,

Basically in my pure ignorance and this being my first project I have used autonumber to be the "ID" number for my records within a bug tracking system.

To be honest I knew this was an issue after I had split my database a few weeks ago but I just couldnt bring myself to solving it until I knew the database would run correctly on users machines + the miriad of other technical issues had been solved.

However now is the time to get this fixed. So basically what I would like to know is, how exactly do you go about creating a unique identifier? How are these often constructed and has anyone had to create a system within one that is currently being used such as in my situation.

I think I would typically just like to have a pure number which is as close to the normal autonumber as possible as I expect this will reduce the amount of code I have to rewrite with my queries and search functions.

[TheSmileyCoder]

I am a bit at a loss to see the issue? I almost always use autonumber as unique identifiers, unless there is a specific demand for something else. In most (95%) cases I would recommend using autonumber.
The only real exceptions I can think of, is if you absolutely need the ID field to be without gaps (gaps left behind by users starting a new record, drawing an autonumber, then not saving their record), or if you need the numbers to 100% accurately represent the order in which the records where submitted.

If you need to create a semi-autonumber yourself, you can use code like this to get the next number in the line:

lngNewID=Dmax("ID_Field","myTable")+1

which would go somewhere in the before update event of your form.

[munkee]

I thought the issue with using autonumber as an identified is that when you do a compact and repair, if there have been records deleted and you do have gaps in your autonumber sequence it reclaims these gaps?

Edit,

Actually I think I have got the wrong end of the stick here.

Does it work like this - Whilst it does reclaim the open record space, it does not reshuffle all of the autonumbers back in to one block.

For example prior to compaction after some deletes my autonumbered records are:

1
2
4
7
8
10

After the compact and repair is carried out, this is what does not happen:
1
2
3
4
5
6

Instead does access just say, in the future when a random autonumber is needed you are able to use 3,5,6,9 as they have been reclaimed in the compact and repair?

[TheSmileyCoder]

Access will not change existing (used) numbers when you do a compact and repair. That would be completely bonkers, as if you had a related record, related to the ID field, of the primary record, you would now be screwed.



Doing a Compact and repair will also not allow re-use of used autonumbers. So if you deleted record nr 3, you can still not create a new record with ID=3. They are not "reclaimed" so to speak.

One exception to this, is if you delete ALL your records in the table, and do a compact and repair, access will set the next autonumber to be 1. (Which is actually quite usefull if you have made alot of test records before sending your database into production)

[munkee]

Thank you for clearing all of this up TheSmileyOne. I think it is fine then for me to leave my current numbering system in place. I though it seemed a bit over the top for it to re-arrange all of the numbers but after reading so many websites it seems quite easy to confused especially when there are apparent huge warnings with using autonumbers, but clearly this was meant to be in reference to people who want a purely sequential order with no gaps and not someone like myself who just wants the unique factor.