Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Database Design / April 2007

Tip: Looking for answers? Try searching our database.

Primary Key Problem

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Rose - 30 Apr 2007 20:40 GMT
I am working in a database in which the original creator designed a primary
key and relational ID with a naming formula.  Overtime this has not worked
because people have changed the naming formula and some other mishaps.

My question is I would like to currently establish a primary auto ID in the
Main Table.  This part I can do.  However, instead of changing each
relational record individually is their a code or something I can do to force
the new ID change into all the relational tables?

Signature

Rose

KARL DEWEY - 30 Apr 2007 20:58 GMT
The best way I know how is to backup the database.
Break the relationships. Add new fields for the primary and foreign keys.
In an update query join the old primary and foreign keys and update the new
foreign key field with the new primary key.  Re-build the relationships.
Signature

KARL DEWEY
Build a little - Test a little

> I am working in a database in which the original creator designed a primary
> key and relational ID with a naming formula.  Overtime this has not worked
[quoted text clipped - 4 lines]
> relational record individually is their a code or something I can do to force
> the new ID change into all the relational tables?
John W. Vinson - 30 Apr 2007 21:20 GMT
>I am working in a database in which the original creator designed a primary
>key and relational ID with a naming formula.  Overtime this has not worked
[quoted text clipped - 4 lines]
>relational record individually is their a code or something I can do to force
>the new ID change into all the relational tables?

If your old primary key is usable, you can (with some hassle).

BACK UP YOUR DATABASE first, you'll be taking a chainsaw to the design!

Add an autonumber ID to the main table as primary key. You'll probably want to
create a new empty table with all the fields of the old, plus the new
Autonumber; run an Append query to migrate your data into the new table (sort
by the old ID if the order of records is important to you).

Add a Long Integer foreign key to each related table. DON'T join them yet!

Now run as many Update queries as you have child tables. Join the (new) main
table to each child table by the *old* ID; update the foreign key field to the
new autonumber field.

Then, delete all the relationships on the old ID, and establish new
relationships from the autonumber to the foreign key.

If the old key is "human meaningful", or if it has appeared in printouts and
may be needed in the future, leave it in the main table, but I'd say remove it
from the child tables.

Test all your forms and reports. Swear, grumble, groan and fix the errors that
you will find. Iterate until done.

Good luck - this is probably a Good Thing to Do but it can be a real hassle!

            John W. Vinson [MVP]
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.