linking tables and relationships

Discussion in 'Access General' started by Puppet_Sock, Mar 27, 2013.

  1. Puppet_Sock

    Puppet_Sock Guest

    So I have a legacy Access database. And the tables are included in the
    database file. And there are bunches of relationships defined for lots
    of
    the tables.

    Now I want to split the tables off into a separate file, then link to
    that
    separate file. I have the usual reasons for wanting to do that.
    Database size, sharing among multiple users, etc. Some of the
    tables are getting quite large and I may put each of those in its
    own separate file.

    How can I get the existing relationships to apply to the linked
    tables?
    That is, other than manually recreating them on the newly linked
    table.
    I can do that, but it will take a long time. The guy who wrote the
    existing database was very fond of adding relationships.
     
    Puppet_Sock, Mar 27, 2013
    #1
    1. Advertisements

  2. The Database Splitter Wizard will do all that for you. It will preserve the
    relationships just fine!

    If you want to do it manually, make a copy of the database. This will be your
    new frontend. (KEEP THE ORIGINAL FOR SAFETY!!!)

    Now create a new empty database and use External Data... Import to import all
    the tables. There's an option that imports the relationships - use it.

    Now open your new frontend, take a deep calming breath, and DELETE all the
    tables. Then use External Data... Link to link to the tables from their new
    location; compact and repair the database to recover the space formerly
    occupied by the tables.

    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Mar 27, 2013
    #2
    1. Advertisements

  3. Puppet_Sock

    Puppet_Sock Guest

    Thanks John. That`s pretty cool.
    Socks
     
    Puppet_Sock, Mar 27, 2013
    #3
  4. Puppet_Sock

    Puppet_Sock Guest

    On Mar 27, 12:15 pm, John W. Vinson
    [snip]

    To give something back: Supposing you want to move the back end
    file created by the Splitter Wizard. It's quite simply done. Move the
    back end file. Then open the front end file. Then right click on any
    table and select the Linked Table Manager. Select all the linked
    tables, and update them. It will prompt you to select the new
    location.
    Presto laundry-o, you've got tables linked to the new location.

    When you do things the way the Access developers have
    prepared for, they go pretty smoothly.
    Socks
     
    Puppet_Sock, Mar 27, 2013
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.