I have a small registration software i made. It basically will just let the person sitting on the software type in details like Name, location, college etc and when the "Enter" is hit, all that info typed on screen goes into an access DB and all the fields get cleared to type in the next record.

Here my Record ID property is set as autonumbered in access. While i run and add records into the software everything works fine say like this :

ID Name Age
1 aaaaa 19
2 bbbbb 21
3 ccccc 20
4 ddddd 18
5 eeeee 22

Now i want to run it in actual live mode and i delete the records 1 - 5 from the access DB, the next time i add a new record through the software, it starts from 6. I also tried deleting 3 & 4 from the list, but instead of rearranging or autorefreshing the numbers it shows like this :

ID Name Age
1 aaaaa 19
2 bbbbb 21
5 eeeee 22

Is there a way i can fix this small glitch ? i.e if i delete any record from between, then the auto number should refresh all the numbers to one after another like this :

ID Name Age
1 aaaaa 19
2 bbbbb 21
3 eeeee 22

Regards
Dhanesh.

That's is actually not a glitch, it's how auto increment works and it's an important feature to ensure data integrity. Say for example, you have another table to keep track of the user's history, maybe last active/visit. It should look like this:
YOUR ORIGINAL TABLE:
ID Name Age
1 aaaaa 19
2 bbbbb 21
3 ccccc 20
4 ddddd 18
5 eeeee 22

HISTORY TABLE:
HistoryID UserID LastVisit
1 1 12/04/2007
2 1 13/04/2007
3 3 15/04/2007
4 2 20/04/2007
5 5 21/04/2007

UserID in history table is referring to your user table. By referring just the ID, you can create a lot of relating table to keep things, and you only need to update 1 table if the user happen to change it's age, since all referring table point to the same ID. That's how relational database works.

Now say you wanted it to works the way it you proposed after deleting a few records:
ID Name Age
1 aaaaa 19
2 bbbbb 21
3 eeeee 22

At this time, in history table, entry 3 which is suppose to refer to "ccccc", is now referring to "eeeee", which is wrong. That's why ID has to remain unique and permanent, representable for each record to ensure that these kind of problem does not happen.

Imagine, say your phone bill account number, which is also auto increment and unique, happen to change when the guy before you decide to disconnect his phone, and the phone company deleted his record.

So, in this case, you are assigning a unique ID to your users, so it has to remain permanent per user. Don't try to fix it, but instead try to exploit it's usefulness.

I wanted to say the same thing about this issue, even though when I started playing with databases, I also thought that I want the numbers to generate from 1 2 3 and after deletion of 2 to include 2 later, that it would look better.. but with time I understood that it is pointless to look at your auto increment ID and I recommend to keep your eyes away form it and ignore the number, who cares about the number, usually the number, the unique id isn't supposed to be shown, the main thing in the database is the data, but an AutoNumber ID is really good to find and select, update etc. your data, so to conclude, don't care about it, this is the way it needs to work.

EDIT: In the Access DB the Age field is specified as number and not text. When i try to add the fields from the frontend it acts a little weird. I cant explain how weird cause when a piece of code to add a row is put in, then it shows me a conversion error. Duno how to explain this but if you are familiar with such an error please tell me how to correct it. I use VB >NET 2005.
Thanks

Thanks for getting that off my brains guys , thought it was something from my side. Newayz, the main intension of all this fuss was that the database after getting filled with data would be saved as an excel file for further use. So my concern was not redundancy errors here, tho i understand what this could do in a major application.

Regards
Dhanesh.

This post has been edited by dhanesh: Apr 28 2007, 06:00 PM

If that's what you want, you've 2 option.
1. Create another column named "No", integer. Whenever you update the database, update the column also with incrementing numbering. Or only update the numbering before you export to excel

2. If you're generating the excel from your coding, then manually insert a column whenever you generate the excel file, that column should be the numbering.

Hi, i have a similar issue but the other way round.
I have an MDB database with a primary key of an auto number and i'm connecting through VB.NET 2005 using drag and drop datasets. In MS Access if i delete a table entry then add another record the autonumber icrements as though the previous record was not deleted (which is good) but in VB.NET when i go to add a new record it will assume the number of that previously deleted within MS Access but i would like it to continue the incrementation in sync with Access as i want to move record from the active table into a completed table but maintain integrity as the auto number is the job number.

Any help would be appreciated.

Don't quite get you. Can you lay out a bit of example to make it clearer?