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
shena 0 Junior Poster in Training
vb5prgrmr 143 Posting Virtuoso
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
kinwang2009 7 Posting Whiz in Training
See my attached help. Here my userID will might help you.
Thanks
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.