reindexing database keys

[Glade]

Hello all,

I have a database that was somewhat poorly designed. The tables I need to reindex look like this:

Table 1 - Products
------------------

Key Name Description

Table 2 - Product Items
-----------------------

Key Product_Key Item_Name Etc.

Product_Key is a foreign key to the primary key of table 1. The Product_Key is a simple integer number that corresponds to the position of the record in the database. For instance, the first record should have a key of 1, the second a key of 2, etc. (This sequential numbering is not essential, by the way, everything works fine with gaps in between).

Problem is, as records were deleted from the database, the numbers didn't change, so there are large gaps in the number system. For instance, the first record in Table 1 has a key of 23. I am rewriting the program that uses these tables, I am wondering if there is a function in Access that will let me reindex all of the records but maintain the foreign key relationship? For example, the reindex process would take my primary key of 23 and make it 1, but it would also change all of the foreign keys in the second table from 23 to 1.

Any help would be appreciated,

-Ade

 

[RickSpr]

First, check the following assumptions I'm making:
1. Products.Key is an Autonumber field, and is the primary key.
2. The relationship from Products to [Product Items] is one-to-many, and enforces referential integrity.

If Products.Key is not an Autonumber field, you can simply update Products.Key directly, either in a datasheet or with code. Just make sure the relationship cascades updates while the update is occurring.

But assuming Products.Key is an Autonumber field, it's a good deal more difficult. Are you sure this is really worth doing? With over 2 billion values, it's not likely you're running out of values for the Key field. And Autonumbers, under best practices, should not be exposed to users in any significant way, so are the gaps really a problem?

If you want to continue, proceed as follows. Make sure you do this on a copy of the database; if anything goes wrong, it might be impossible to repair the damage. Warning: This procedure is not sufficient if the Products.Key field participates in any other relationships than that with the Product Items table.

1. In the Relationships window, note the properties of the relationship between Products.Key and [Product Items].Product_Key. Then delete it. (You'll recreate it later, using the new key field.)
2. Open Products in Design View. Highlight the Key field and copy it to the clipboard. Then change the Key field data type to Number, and save the table without closing it. Insert a blank row after the Key field, and paste a copy of the unmodified Key field in it. Change the field name to NewKey.
3. Save and close the modified Products table. When you do, Access will automatically populate the NewKey field with values starting at 1.
4. Create an Update query on the join between Products and [Product Items]. Delete the autojoin, if any, between the Key fields. Draw a join line from Products.Key to [Product Items].Product_Key. Then drag [Product Items].Product_Key to the grid, and enter [NewKey] in the Update To: line. (The brackets are required to keep Access from interpreting this as a string.)
5. Run the query. This updates the Product_Key values to the corresponding NewKey values. At this point, you should look at the [Product Items] table to verify the results. You can then discard the update query; there's no need to save it.
6. Open the Products table again in Design View. Set the NewKey field as the primary key.
7. Examine the Indexes window. Change any occurrences of the Key field in multicolumn indexes to NewKey. (Don't change any indexes that contain only the Key field; they aren't needed, and will be deleted in the next step.) Then close the Indexes window.
8. In the Products table, delete the Key field, and rename the NewKey field to Key. Save the table design and close it.
9. Recreate the deleted relationship between Products.Key and [Product Items].Product_Key.

I tested this procedure, and it accomplishes the goal, but I can't be sure that it has no unpleasant side effects. At this point you should conduct tests of the application. Rick Sprague