Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69

    Red face Unanswered: query takes forever to finish

    Hello,
    I have an Access db of 361 MB, and I recently create a query for 2006 based on the query for 2005. The tables that my query is uses are linked tables from SQL.

    My old query executes in 1 min, and a new query takes forever to finish.
    When I moved them to Temp.mdb, both run under 1 min.

    I do not understand what possible wrong with my Access mdb of 361MB.???
    I even compacted it, still my 2006 query takes forever to finish.

    Please help.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You'll get more help if you post the queries in question.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Query running slow

    361mb? Wow - that's pretty significant in size for having linked tables! We use linked SQL Server tables and the largest mdb file we have is maybe 40meg. Do you have any MSAccess tables which are in this mdb and causing it to be so big?

    I assume you've indexed all the tables which still would not answer why the same linked tables run faster in an older query but not a new one in the 361 mdb (providing the same fields and parameters are there.)

    I'm wondering, is there something different in the new queries verses the old ones or did you just copy the same queries to a new mdb and now they run faster? If you tried deleting the linked tables and re-linking them in the old mdb, does that help?

    I guess the question would be: Would a 361 meg mdb affect the execution time of a linked table query where that same query would run faster in a Temp.mdb (and what is the size of the Temp.mdb)? Also, are you opening the 361 meg mdb with the shift key to make sure you are bypassing any code just in case (not that I think this would matter)?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    I had to recreat my database, and when I did it, all working just fine. I guess some of the indexes were missing. I did before the compact of this database, about 3 to 4 times, may be it did not work as it had to?

    Thank you all for helping me.


Posting Permissions

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