Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linked table performance (2003)

    I won the lottery and inherited a very, VERY ugly database.

    It has LOTS of data. So much so that some of the tables are actually links from other .mdb files because they won't all fit in one.

    The users do lots of reporting and data manipulation on this data. I don't want them, however, to be anywhere near the true data tables where a simple delete renders it useless.

    What sort of performance hit would I take if I only allowed them to access the data through a front end linked to all the tables in the original .mdb? Remember, some of the new links would be linked to existing links.

    Make sense?

    Is there a better way?
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked table performance (2003)

    Hi Morgan,
    Firstly, there would be no linking to linked tables (I don't even know if you can). Access will create the link to the source database directly, just like the database you are linking to is linked. This includes ODBC linked tables as well as Access database tables. With ODBC linked tables, Access will use the same ODBC data source as is used by what you are trying to link to, so it must be a "current", registered connection.
    Another way would be to use ADO connections for all your data INSERTS, UPDATES, etc. But I would only recommend this, and then tenuously, if you and your data are separated by vast distances.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Linked table performance (2003)

    As I understand it, you will be splitting your database into a frontend and backend (this is a good thing). The backend database (I guess this is your original database) should now only contains its own tables and relationships (delete any links to other tables). The new frontend will contains links to tables in new backend, plus links to any tables in other databases.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Linked table performance (2003)

    >>Firstly, there would be no linking to linked tables (I don't even know if you can).<<

    Actually you can do it! Not something I would do routinely, but there are a couple of situations were it proved useful.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked table performance (2003)

    Hi Mark,
    Would you enlighten me please? Everytime I've tried to link to linked tables, the connection between the original frontend and the backend is copied (my guess) and used as the connection between the "new" frontend and the backend. I can see when there would be occasional usefulness, but does it necessitate the frontend, of the linked tables one would be linking to, be open?
    Thanks.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Linked table performance (2003)

    The only situation I "link-to-linked tables" is one in which I'm transferring information between 2 applications. Let's say I have 2 frontend applications: App1Prog and App2Prog. From App1Prog, I need to link to the backend used by App2Prog. Now, I happen to know where App2Prog is located, but where its backend is located is somewhat problematic; in this one situation, it could actually be an Access .mdb database or a SQL-server database! So rather than worry about it, from App1Prog I just link to tables I need in App2Prog, and it takes care of itself.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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