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

darrenm
darrenm asked
on
Medium Priority
356 Views
Last Modified: 2010-05-19
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)

Any suggestions/responses would be helpful.

Darren Morris
Supreme Court of Victoria

Comment
Watch Question

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.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

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

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.