Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Red face Unanswered: Datasheet Opens Slow w/Linked Network Tables

    When tables are linked from a network drive in Access 2000, I experience slow performance when opening datasheet view of any non-linked table, regardless of it size. The more networked tables linked, the slower datasheet view loads. One minute is the worst time I observed with 5 linked tables. There is no slowness with form view and queries run fast. Linked tables from my C drive do not produce this slowness. As soon as I unlink network tables and compact the database, datasheet view loads instantly.

    In testing, I've put my own Access 2000 database on the network to link to, and also linked to several other 2K databases, all with similar results. It took 15-30 seconds to view any of the non-linked tables.

    I would like to know if this is a common behaviour with Access 2000. Access 97 did not perform in this way.

    Thanks.

  2. #2
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147

    hmmm

    That's a strang one.

    Do you have any relations between the linked and non-linked tabled?

    Perhaps Access looks to the linked table in order to set up the auto sub-datasheet facility?

    Can't think of anthing else..

    Cheers,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    There are no links between the network tables and "local" tables. I experimented with different databases and different network tables, with the same results. Perhaps this odd ACCESS 2000 behaviour was meant for me alone.

    Thanks for your previous comments.

  4. #4
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    This could be it - my hunch may have been correct!

    Found this:
    Sub datasheet Name property set to [Auto]
    Thanks to Microsoft for compiling much of the following text.

    Access 2000 performance may not be as good as it was in Access 97 for a number of reasons, most of which are outlined in the following articles:

    Q261000 - ACC2000: Slower Performance on Linked Tables

    The above article indicates that if the database has many linked tables that also have many relationships, and the table that you are opening has its sub datasheet Name property set to [Auto], this can make the table slow to open. Subdatasheets are a new feature in Access 2000 Therefore, you are more likely to notice this behavior after you convert a database from an earlier version.

    It is recommended that we set the sub datasheet Name property on each table in the back-end database to [NONE].
    from here:
    http://www.granite.ab.ca/access/a2kslow.htm

    HTH,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

  5. #5
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Smile

    Many thanks for your help. Slow opening of tables in Access 2000 databases that have one or more linked tables was resolved by following advice in article Q261000.

    I opened each non-linked table in design view, clicked View, Properties, and then changed the Subdatasheet Name property from Auto to None.
    After saving the change, the table loads in datasheet view immediately. I only made the change to my own non-linked tables.

    The problem has been resolved in dBforums. Life is good once again.
    Jerry D.

  6. #6
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Angry Not so fast!

    Now that I have the solution to slow opening of tables in a database that has other table(s) linked (Microsoft article Q261000), I realize that the curse has not lifted.

    Every month when I create new tables from queries, I am faced with the same issue: take the time to change Subdatasheet Name property to NONE or endure slow opening of datasheet view.

    There is a script provided in the article and it does the job of changing the property in all database tables (in about 2 minutes in my case).

    Conlusion: life is not as good as I thought. Microsoft needs to fix this.

  7. #7
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    Hmm, bugger. What a nuisance.

    Without knowing much about your environment, a couple of possible workarounds which may or may not be applicable.

    Consider using continuous forms instead of datasheet view in your forms. Can do everything data-sheet view can and are more customisable. Might not take as long to open?

    In my experience, creating tables from queries as a regular occurence can be avoided. Obvisouly there are different scenarios, but why not just access the query when required, rather than creating a new table?

    If you want to take a snapshot of the data at a certain time, then perhaps just export it to another database or format where you can access it at any time - rather than creating new tables all the time in your main application.

    Just a few suggestions - my not be applicable at all in your application but thought I'd list them anyway.

    Cheers,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

Posting Permissions

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