Solved

Resequencing Access table records

Posted on 1997-11-19
1
301 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
1 Comment
 
LVL 9

Accepted Solution

by:
cymbolic earned 50 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

777 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