Results 1 to 6 of 6

Thread: Access Query won't run, getting error.

  1. #1
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Access Query won't run, getting error.

    Hello and thank you in advance if you can help,
    I am using Access 2019 on a PC.
    I have a database that under properties is said to have a size of 2.87 MB. All my files are linked, four data files as text imports (two 1 million + records, and the other two small, a few thousand), 8 lookup tables as excel imports (all are pretty small files), 8 queries, and 2 union queries. The newer queries have been running slow to open, and the last one I created will not open, I just get an error, "The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2 GB), or there is not enough temporary storage space on the disk to store the query result."
    A lot of, okay most of, my queries build on one another and achieve different tasks.

    I have done a compact and repair. Is it possible that I have already achieved a max size when the overall database size is still so small? Is it time for me to send one of the base queries to a second database for further analysis, or could it be something else because my overall database size is still so small?
    Thank you in advance for any help you can provide,
    Maggie

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,347
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Access Query won't run, getting error.

    It's a little hard to know what you mean when you say your have linked files. Are these files in your database or not in your database? How much data are you querying for when you get this message - what is the expected size of your dataset (if possibly, converted into bytes, kiloBytes, or megabytes) For instance, 2 million records of anything is probably more than 2.87 megabytes.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access Query won't run, getting error.

    xenou,
    I bring my files into the database by using the "Link to the data source by creating a linked table.". That way I can import a lot of tables (text files & excel) without maxing out the capacity for an Access database. I have two other databases that deal with different aspects of the data, creating a lot of queries and table outputs, and these have all worked great, but this one started running really slow, and, as I said, the last query won't open. I did go in and remove one of the tables I brought in, saved the changes, and then opened it again, and it ran and opened the datasheet view, but when I link the table, bring in one of the variables, and try to filter accordingly, it will no longer open and I get the error. This query contains 1,115,1867 records, and the query I am trying to bring in has 100,873, though it is a reference to another query with the same number of records as the first. I just need to bring in one field from this query to enable me to filter out those records. I am evaluating a dataset and parsing out “valid" records from questionable records, then running a series of tests on the questionable records to review the data.
    I hope this explanation helps,
    Maggie
    Last edited by Maggie Barr; May 9th, 2019 at 04:09 PM.

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,347
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Access Query won't run, getting error.

    I guess you could post your query if you would like further analysis. You might also just consider taking the message at face value and checking your memory usage and disk usage while you are running the query.
    Last edited by xenou; May 10th, 2019 at 08:32 AM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access Query won't run, getting error.

    xenou,
    I made another access database, went in my original, and used a make table query on the base query I had that had already brought in fields from multiple lookup tables, and I linked to that table in the new database, so things now seem to be running smoothly. The queries I was running really must have been just using up to much memory to run.
    Thanks,
    Maggie

  6. #6
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,347
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Access Query won't run, getting error.

    Okay, cool. That's a general purpose solution that is often good when a query takes a long time to churn - first pare down the results to a smaller, relevant subset of data you really need to work with. Then work with your smaller set of data saved in a local table.

    Note that this kind of "step-wise" work can also be scripted, if its needs to be used on a regular basis: ie.,

    • run query 1 (for make table)
    • run query 2 (for updates)
    • run query 3, 4, 5, etc. etc.
    • run select query or report


    So all of the above could be run in sequence automatically in a vba script or as a macro, saving you the need to click and run multiple queries in succession.
    Last edited by xenou; May 13th, 2019 at 03:34 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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