Results 1 to 7 of 7
  1. #1
    vector39 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    May 2017
    Posts
    76

    Am I able to replace table without removing relationships to other tables?


    Hi everyone,

    Just a quick question, so I have a bunch of tables with various relationships but they all point to this one main table called tblCompanyInfo. I made a copy of this main table and all the field names are the same, the only difference is I've changed some of the values in each record and called this table tblCompanyInfo_modified. Is there an easier way to replace the present main table with this modified copy without deleting the relationships and reconnecting them? If this is how it's got to be then that's okay but just wondering if there's an easier way. Thank you!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No, there is not an easier way.

    Why didn't you just change the data in the original table? Are you going to delete the original table?

    Do you have queries and forms and reports based on the original table?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    On fly:

    Code:
    UPDATE tblCompanyInfo ci INNER JOIN tblCompanyInfo_Modified cim ON cim.PrimaryKey = ci.PrimaryKey
    SET
         ci.Field1 = cim.Field1,
         ci.Field2 = cim.Field2,
         ...
    NB! It updates only records, for which same PrimaryKey (or primary keys complect) exists in both tables. When you have records added or deleted in modified table, you have to run additional APPEND and DELETE queries. And with append query you may have problems, when the PrimaryKey is an autonumber.

    With autonumeric primary key, when there are no gaps in primary key numbering, you can also delete all records from tblCompany, reset autonumber so it starts with minimal autonumber value in tblCompanyInfo_Modified, and use an INSET INTO query to fill tblCompanyInfo with data from tblCompanyInfo_Modified.

  4. #4
    vector39 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    May 2017
    Posts
    76
    Quote Originally Posted by June7 View Post
    No, there is not an easier way.

    Why didn't you just change the data in the original table? Are you going to delete the original table?

    Do you have queries and forms and reports based on the original table?
    yeah im going to delete

  5. #5
    vector39 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    May 2017
    Posts
    76
    Quote Originally Posted by ArviLaanemets View Post
    On fly:

    Code:
    UPDATE tblCompanyInfo ci INNER JOIN tblCompanyInfo_Modified cim ON cim.PrimaryKey = ci.PrimaryKey
    SET
         ci.Field1 = cim.Field1,
         ci.Field2 = cim.Field2,
         ...
    NB! It updates only records, for which same PrimaryKey (or primary keys complect) exists in both tables. When you have records added or deleted in modified table, you have to run additional APPEND and DELETE queries. And with append query you may have problems, when the PrimaryKey is an autonumber.

    With autonumeric primary key, when there are no gaps in primary key numbering, you can also delete all records from tblCompany, reset autonumber so it starts with minimal autonumber value in tblCompanyInfo_Modified, and use an INSET INTO query to fill tblCompanyInfo with data from tblCompanyInfo_Modified.

    thank you! worked like a charm!

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So only needed to change data in original table after all? Why the modified table? Is this going to be a recurring process?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    vector39 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    May 2017
    Posts
    76
    Quote Originally Posted by June7 View Post
    So only needed to change data in original table after all? Why the modified table? Is this going to be a recurring process?
    Actually I needed to make a copy for a colleague but I didn't want to share certain info, so I created a modified version of the main table. But the solution ArvilLaanemets provided works greatly.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-21-2017, 01:07 PM
  2. Replies: 4
    Last Post: 09-15-2014, 05:39 AM
  3. Replies: 3
    Last Post: 06-07-2012, 07:05 AM
  4. Removing Shortcut menus on tables.
    By Desstro in forum Access
    Replies: 4
    Last Post: 11-04-2010, 12:19 AM
  5. Replies: 1
    Last Post: 07-27-2010, 08:02 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums