473,320 Members | 1,732 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

374 256MB
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.
Sep 6 '10 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. lngNewID=Dmax("ID_Field","myTable")+1
which would go somewhere in the before update event of your form.

4 1605
TheSmileyCoder
2,322 Expert Mod 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. lngNewID=Dmax("ID_Field","myTable")+1
which would go somewhere in the before update event of your form.
Sep 6 '10 #2
munkee
374 256MB
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?
Sep 6 '10 #3
TheSmileyCoder
2,322 Expert Mod 2GB
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)
Sep 6 '10 #4
munkee
374 256MB
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.
Sep 6 '10 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Steve Bishop | last post by:
I would like to pass an unique identifier (UserID) to a Crystal Report from a SQL stored procedure. I found an article from Business Objects about this issue, but I couldn't get my head around it's...
8
by: Nitin | last post by:
Hello, We are trying to figure out how to get a unique identifier for a machine. Our application is a C# windows application that talks to our server via a webservice. Every time our webservice...
3
by: deko | last post by:
I have a situation where data is being imported from external tables. The Import Wizard (File > Get External Data > Import) is used to create and import tables from different sources (txt, xls,...
4
by: ba.hons | last post by:
Hello all, Was wondering if anyone could provide some info on what could be a possible solution to a problem am having. I have to generate a Unique Identifier in C# which I will use to assign...
2
by: Ken | last post by:
Hi, I have a form whose control source is a view from SQL server 2005 database. The view has a primary key that is a unique identifier field with keys generated by newid() function from SQL server...
8
by: DaTurk | last post by:
Hi, I was just curious how you would go about creating a unique identifier with 3 ints.
14
by: Steven D'Aprano | last post by:
I have an application that will be producing many instances, using them for a while, then tossing them away, and I want each one to have a unique identifier that won't be re-used for the lifetime...
4
by: Rob Stevens | last post by:
Is there some sort of unique identifier in every treenode that is consistent? I was looking at the handle of every treenode, but it appears that the handle changes everytime the tree is built. ...
4
by: Mufasa | last post by:
I'm looking for a way to get a truly unique identifier for a machine for our client software. I'd like to have it so that there's little or no setup by the end user. (We set up the machines and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.