Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Resequencing Access table records

Posted on 1997-11-19
1
Medium Priority
?
330 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

0
Comment
Question by:darrenm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 9

Accepted Solution

by:
cymbolic earned 150 total points
ID: 1960221
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.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question