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
Solved

Resequencing Access table records

Posted on 1997-11-19
1
304 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

860 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