Member Avatar for shena

Hi,
I created tables in ms access whereby all the tables have index. In a table, if I delete a record (lets say index number 10) and then write a new record through program, the new record doesn't follow the last running number (where last index is now 9, new index is to increase by 1, become 10). Instead the new record's index increased by 2(the index is 11). It suppose to be index 10. How can I accomplish this? Please anyone help me with this problem.
Shena

  • 3 Contributors
  • 2 Replies
  • 104 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by kinwang2009

Recommended Answers

All 2 Replies

Member Avatar for vb5prgrmr

Using an autoincrement field in access this will happen as it should happen. Now why this happens is because not everyone knows how to setup a parent child relationship within access. Think about this. You have a parent child relationship but don't have it setup through access, so when you delete the parent records and leave the child records then reuse that pointer, now those old child records are pointing to your new parent record and may not have a thing to do with it.

Now with all that said, you can get around this behavior by design by compacting and repairing the datbase. However, if you add a new record before you compact, (1,2,3,...,9,11) you will not be able to recover the unique ID 10... When that is not unless you take off the unique constraint and the index on the field. Then inserting the id of 10, reindexing and then adding the constraint...

Good Luck

Member Avatar for kinwang2009

See my attached help. Here my userID will might help you.
Thanks

Login_Example_with_MSAccess_DB.zip (16.19 KB)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.