Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2006
    Posts
    47

    Unanswered: Access 2000 Query Limits

    I have written a set of queries that have worked before but know seem to hang and never complete. The approximate record size is about 5 Million at its highest point but it aggregates back down to about 50,000. Additionally, it is done in about 3 sub queries.

    I have read through the specifications and previous post and the database is under 2G and I keep reading that there is no record limit. Additionally, it was working at one time and now it isn't. I have also used the Compact and Repair several times to keep the size down and repair any possible corruption.

    Could it be a bad day for my computer or am I pushing to hard in the Query and have broken it?

    In case it helps the first query takes two linked tables one with ~500,000 records and references 3 fields in another linked table with ~15,000 records and returns about 15 fields from each table for a total of ~750,000 records.

    The second query takes this output and links to a local table with ~1,000 records and references 2 fields and adds 1 more field but doesn't add any records.

    The last query takes this output and links to a local table with ~10,000 records. This query expands to the ~5 Million and aggregates down to output about 50,000 records.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    5 million records at any given time is way, WAY more than access should reasonably be expected to handle.

    Based on what you're describing, I'm not sure I'd even recommend doing this in SQL Server...
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    With the best will in the world asking JET to search throiugh 5 million records is going to be slooooow, doing it over a networked drive is going to be seriously slooooooooooooow. I think you are just asking too much of JET, and the way it integrates into Access. It isn't an Access problem its a JET problem.

    As Teddy suggest consider putting the data into a server, if you have the budget then SQL server is a good choice, MSDE or SQL server personal edition may be an option. There are other options like Postgre & MySQL...

    Even if you do switch to a server backend you need to rethik the application and move the query processing off your PC and onto the server hosting the SQL engine. Its not something to be done casually.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - I would second (third?) the advice above - a server dbms would be the ideal.

    If this is not possible, perhaps you can dump the results of the first two queries into local, indexed tables and run from those. Effectively create adhoc summary tables. If the results don't need to be totally current you could even schedule this for over night.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2006
    Posts
    47
    Thank you for all of the advise. I will have to look into getting some of this done on the server and in the meantime I will try creating the ad hoc summary tables on my PC. Thank you again!

Posting Permissions

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