Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linked Tables (2003 11.5614.5703)

    I'm working on a database with linked tables (front end - back end). When my linked tables are on my local hard drive things work fine. When I move them to a network drive and relink with the Linked Table Manager, things REALLY slow down. Even just opening forms in design mode (seemingly not needing the tables), editing them, and saving them takes too long (e.g., 15-20 seconds to open or save). Again, things work fine when the linked tables are local. I don't think there's a problem with the network or the network drive -- moving/copying files to and from there works quickly. I've tried the suggestions provided here but they don't seem to offer any significant improvement.

    If someone else is accessing the linked tables at the same time using their own copy of the front end, things grind to a virtual halt (the opening and closing mentioned above takes several minutes). I'm guessing I'm completely overlooking some no-brainer "law" of shared databases, but this brain is at a loss at the moment. Are there some "usual suspects" I should be dealing with? Do options like refresh intervals make a difference? What are the pros/cons of the various record locking options?

    Any suggestions would be greatly appreciated!

    Thanks.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Linked Tables (2003 11.5614.5703)

    This sounds much like the locked LDB file issue where Access tries to delete the LDB file for the database a number of times before deciding it's the existing one should be used, but if you've got a form open that links to a simple table, then that shouldn't be a problem. What is the performance like just using the database, rather then trying to make design changes?
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Tables (2003 11.5614.5703)

    Wendell,.

    Thanks for your response. The performance is equally sluggish when I'm "just using the database." It's almost tolerable if I'm the only one accessing the tables, but it really goes south if someone else has their front-end running and is linking to the back-end.

    When you say "if you've got a form open that links to a simple table, then that [the locked LDB file issue] shouldn't be a problem", do you mean employing the "trick" of opening a form that links to a back-end table at the outset and then leave it open (albeit hidden) throughout the session? If so, I am doing this EXCEPT that the form does not link to a back-end table. Is that what I'm missing? Easy enough to add and try it, but I'll have to wait until I'm back at work on Monday where the network resides (it's late now but I'll try it on my little home network tomorrow to see if there's a performance improvement).

    Thanks again.

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

    Re: Linked Tables (2003 11.5614.5703)

    Wendell indeed means a form bound to a linked table in the backend; if the form is opened when the database is opened and closed when the database is closed, the .ldb file will remain in existence during the entire session. Opening a form that is not bound to a linked table in the backend will not have this effect.

    Another way to do this would be to open a recordset based on a backend table and keep it open during the entire session.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Linked Tables (2003 11.5614.5703)

    Hans hit the nail on the head - the table you connect to, whether by a hidden form, or by opening a recordset, should be in the back-end. The idea is to force the creation of a lock (.ldb) file for the back-end. Otherwise, each time you open a table, or a form or report bound to a table, Access first checks to see if there is a lock file, and if there is, it tries several times at 1 second intervals (more or less) to delete it. If it's successful, it goes ahead and creates a new one, if not it uses the existing one.
    Wendell

  6. #6
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Tables (2003 11.5614.5703)

    Hans,

    Thanks for the clarification. I'll give it a shot!

  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Tables (2003 11.5614.5703)

    Wendell,

    Thanks for the help. I'll let you know how it works out.

Posting Permissions

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