Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2010
    Location
    Lichfield, UK
    Posts
    65
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Access 2007: Working With Linked Tables

    I have an application written in Access 2007 using VBA and sitting on an accdb database. Following good practice I've split the database to separate the code/forms/reports/queries (front end) from the data (back end). One of the objectives of this was to make maintenance easier.

    However I've just developed an enhancement to the application which requires a new table. So in my development environment I've created the new table in my front end accdb. Now I'm happy that all is working ok I want to move the data in this table to the backend and point the front end to the back end. Using the options Access offers I cannot find a way of easily doing this. If I use the Linked Table Manager the new table does not appear in the dialogue.

    In the end I have managed to do it by:
    1 - Deleting all tables from my Front End database apart from the new one.
    2 - Copying all tables from the backend database to the front end and having to confirm for each one that I want to copy both structure and data.
    3 - Delete the backend database
    4 - Split the database again, recreating the backend db by linking all tables (including the new one) to it.

    This approach seems long winded and prone to error. Did Microsoft design it to handle such a situation in this way or am I misunderstanding how to do this?

    I did look at how I could have possibly linked the new table to the back end when I created it but could not see an obvious way of doing it.

    I guess what I'm asking is what is the recommended way of creating new tables in a database that has already been split.

    Thanks

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The Linked Table Manager can be used to update the path to Linked tables, but is not used to add or remove linked tables.
    To add an Extra Linked table use "External Data...Access"
    externaldata.gif

    Personally I do this:

    • My copy of the database uses a dummy data file.
    • I create the extra table in the dummy data file.
    • I use "External Data" to add an extra link to the newly created table.
    • When all works OK, open the real data file, and use "External Data" to import the new table. Delete records if appropriate, then add it to the Relationships.
    • Distribute the new Front End.

    If I had initially created the table in the Front End, I would open the Backend end, and import the table from the Front End into the Backend. Then Delete from the Front End, and Link to new backend table.
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    Jun 2010
    Location
    Lichfield, UK
    Posts
    65
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Thanks John - now I understand.

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
  •