We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

# Resequencing Access table records

on
Medium Priority
356 Views
I would appreciate a solution to the following problem:

I have setup an small Access data with say 300 records. The main table is
indexed by a sequential number (not a counter) that is allocated manually
each time a new record is added.

What is the easiest way to renumber the main and related tables (one-to-many) when
entering a new record into the range 1-300. For example record 200 exists
but a new entry requiring to be numbered 200 is to be added to the table.
How can it be added with the previous 200 becoming 201 and all subsequent
numbers being incremented by 1.

(I know this flies in the face of relational theory)

Darren Morris
Supreme Court of Victoria

Comment
Watch Question

## View Solution Only

Commented:
Conventionally, you would not use an identity field as an actual value.   That is once you pick a primary key, that subsequently could become a foreign key in other tables, linking their rows to our primary rows, you never want to change it.  Then, if you have an attribute of the row that contais an actual value (like your numbers), it would be another column in the table.  If you are dealing with smaller numbers of rows, you really don't need an index on the field, since a table scan will be very quick anyway.

But, if you must do this, I would get a read only recordset of all essential row columns (the one's you mean to change and the primary key) containing values above the one you are changing.  Then drop the index, increment your values, and update all effected rows, using .execute methods and update SQL.  Then recreate your index.  I think this would be faster than trying to change the indexed field while the index is still active.  Of course, once you get into large numbers of rows, you'll have to ask your user to pack a lunch each time he adds a low numbered row!

Alternatively, while retaining your index, you could get the rows to be changed in descending sequence by your numbered field, then update them back in that order to avoid index key conflicts, assuming you have set this column for no duplicates.

Either way, by design,you have made an application that will not scale up to large numbers of rows and/or multiuser.

Not the solution you were looking for? Getting a personalized solution is easy.

##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile