Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    update problem (2000)

    Hi, I have a table of customers (some 500 plus or so) where they have a numeric PK. Maintaining this list is becoming something of an admin burden. I have found that there is another DB in the office that has the same structure customer table but is regularlly updated. I wish to have my table mirror this updated DB but the primary keys on this other db have different numbers. What can I do to change the numbers in my DB to match without jeopordising data integritiy bearing in mind the customers sit in many records on a contacts table?

    Thanks Darren.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: update problem (2000)

    Assuming that your PK is not an AutoNumber field (which can never be modified once it has been assigned), you can edit all relationships involving this PK and turn on "Cascade Update Related Fields" (you must first turn on "Enforece Referential Integrity", if necessary). You can then update the PK; all foreign keys referring to the PK will be updated automatically.
    Note: be very careful when doing so, and make backups along the way.

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update problem (2000)

    Thanks Hans, this works fine in a mock up I have done but on a copy of the actual the message "fields must be of same data type and same number of rrecords". data type is same, whoever built the db did not put ref integ on intitially?

    Thanks Darren.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: update problem (2000)

    Have you checked that the data types are really the same? For instance, if primary key and foreign key are both Number fields, but one is a Long Integer and the other an Integer, you still cannot enforce referential integrity. You would have to change Integer to Long Integer.

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update problem (2000)

    Schoolboy error Hans, schoolboy error. Thanks Darren.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •