updating and index or primary key

[rene316]

Hello,
I am trying to finish up a program for my company and am trying to add a record number field ie... record 1 of 20.
I am using .recordcount for the "of 20" part and am using the id field from my access database for the record number. However, when I delete a record, the id field does not update ie... I have 5 records. id is 1,2,3,4,5 then I delete record 3, the 4 records left id's are still the same ie.. 1,2,4,5. How do I get the id field to update as well as the total record count. I am probably going about this the wrong way, so any input or alternate suggestions are much appreciated.

Thank You,
Rene

 

[vbrunner]

If you are using an auto-number (long int) field,(I assume this as that is the Access default), then you can't update that field without disconnecting and re-indexing that table.
Rather than change things at this point, simply use .absoluteposition of your ADO recordset instead of the ID field for your "# of" count.

 

[rene316]

vbrunner,
Unless I am using it wrong, absolute position always says 1. It never changes. That is actually why I went to the index field in the first place. Any help on absolute position?

thx,
rene

 

[JoshuaL]

If I understand you correctly you have, for example, 5 records and when you delete records 2,3, & 4 you want record with an ID of 5 to become 2. If this isn't the case please clarify

An ID is not intended to represent the numerical position of a record. It is used to uniquely identify the record.
If a person's drivers license expires, does the DMV give the next person in line that person's drivers license number?

The number resorting isn't supposed to happen automatically the following reason. What if you had properly structured tables which referred to each others IDs? For example, you've got 2 tables, 1 named employee and 1 named insurance_benefit. Each record in the employee table has has an ID called Employee_ID. In the insurance table, each record has an id called insurance_benefit_ID but also has an ID called Employee_ID which refers to the IDs in the employee table. This allows you to keep track of the benefits that belong to each individual employee. Now, if you deleted an employee and expected the IDs to be resorted then all of the IDs in the insurance table would have to be resorted. This would cause way to may overhead and be a terrible performance problem.

Now, I beleive that your record count is a separate issue because it shouldn't rely on the IDs to get a count. It should be the count of all the records in the recordset. Perhaps it's not refreshing when you expect. It would be helpful if you post the code used to instantiate the recordset and code used to delete from the db

Josh