Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linked Tables, very slow (2000)

    Hello

    Has anybody had problems with slow linked tables in Access 2000?

    I am in the process of developing an Access database, I started the development at home and I am continuing at the clients site. It is in two parts with a database.mdb on a server and a client.mdb that will be on each client machine. The problem I have is that retrieving the data in the client through linked tables to the server is very slow, and at the moment there is hardly any data there.

    If I double click a linked table in the database window on the client, it takes about 10 seconds to display 10 rows of data. If I open the database.mdb (from the client machine) and double click the actual table, it loads almost instantly.

    If I copy the database.mdb to the client and link to it, the linked table will then open almost instantly.

    There doesn't appear to be anything obviously wrong with the network. It is a small organisation with about 20 client machines connected a domain. Ping times to the server (which is in the next room) are consistently quick and copying files to/from the server is fine.

    So far I have tried Microsofts suggestions of:
    Compacting both databases.
    Opening a recordset in the database when the client starts up to prevent having to open and close the database each time a query hits it.
    Setting the 'subdatasheet name' property to 'none' for each table in the database.

    The desktop is Windows 2000 SP3, Office 2000 SP3. The server is Windows 2000 Server.

    Any suggestions?

    Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linked Tables, very slow (2000)

    Did you turn off subdatasheets in both the front and back end databases, not just one or the other? And did you turn off Name AutoCorrect on the Tools-->Options-->General tab? I've seen this behavior fairly often in 2000 and the combination of turning off *all* subdatasheets and turning off Name AutoCorrect has always cured it for me. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  3. #3
    New Lounger
    Join Date
    Feb 2002
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Tables, very slow (2000)

    Hello, thank you for a very quick response Charlotte.

    Initially I turned off subdatasheets in the back end. I have just had a look at the front end, which only has linked tables, and the properties already display 'none' for subdatasheets, presumably reading from the back end.

    I have also just turned off Name AutoCorrect in both the front and back ends and it has helped some. Opening the same linked table now probably takes about 5 - 6 seconds, which clearly is a big improvement on the previous 10 seconds. But it still only contains 10 rows of data, and still, if I open the table directly (on the server) or link to a local copy, it is much quicker.

    Any other thoughts?

    Thank you.

    Dirk.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linked Tables, very slow (2000)

    The subdatasheets can be different on front and backend. If you turned them off first in the back end and then linked the tables, you should have inherited the NONE setting from the back end.

    How much quicker? Locally, it should be virtually instantaneous, so if it isn't, you need to go looking for the reasons. Do you have a bunch of lookup fields set as comboboxes in your tables? If so, that can definitely slow things down and it is something that should be dropped after development anyhow. If you want to see lookups, use a query, don't look at the table. Do you have any saved relationships on these tables? It is also possible that you have multiple relationships declared between tables, which can give Access a pain in the processor as well.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Tables, very slow (2000)

    Not in 2000, but in Access 97 on a 2000 desktop on a mixed 2000 and Banyan network, but even when both front and back were on the 2000 network, occasionally it would be excrutiatingly slow. I think it was a network issue with the 2000 network checking permissions constantly -- but, not being part of the network crew, I don't know. I don't think that involved subdatasheets (as I think Access 97 didn't have them, at least, I couldn't find help on them). -- The linking problem _occasionally_ still happens -- rarely, though, so I can't offer much help except to say, "Yup" and "Me too, occasionally."
    thx
    Pat

  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 Tables, very slow (2000)

    Have you had anyone in there to do diagnostics on your network? What you are talking about is so ridiculously slow that I can't believe anything you do in Access could cause it to be that bad.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    New Lounger
    Join Date
    Feb 2002
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked Tables, very slow (2000)

    Once again, thanks for your help.

    A couple of the main tables 'did' have combo box look up fields. I have tested removing these and the performance has improved substantially, the same table mentioned before now displays in about 1 second or so. I have also removed relationships that were on the look up tables, but I don't think you are suggesting that I should remove all relationships right?

    It seems to me that enforcing referential integrity is a good thing, but do you think it would be beneficial to set up the relationships in the client instead?

    Regarding the network Mark, as this is a smallish company and they do not have any IT people on the staff. The network is handled by a local service provider. I was told that it has been checked and is supposedly OK. The person who told me what complaining about the unbelievable amount time it took to log on. I have since discovered though, that his profile is over 1GB in size, so all my sympathy for him has dissolved :-)

    I will give him a little coaching regarding this.

    Anyway, it would seem that the suggestions I've received here have been successful so I thank you all. Of course, having real data in the tables will be the real test, but the improvement from 10 seconds to 1 second is very encouraging.

    Regards

    Dirk.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linked Tables, very slow (2000)

    I'm not sure what you mean about relationships "in the client". Relationships are a good thing, but you don't necessarily need everything to be related directly. You can only enforce referential integrity (also a good thing) between tables that are local to the same database. In other words, if you are linked to a back end, then only tables within that file can have referential integrity enforced, and that's where the saved relationships should be created. You can create relationships in the front end at well, but again, only tables that are local to the front end can have referential integrity enforced there. The front end inherits the relationships, including RI, from the back end.
    Charlotte

Posting Permissions

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