Access Query won't run, getting error.

[Maggie Barr]

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

 

[xenou]

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.

 

[Maggie Barr]

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

 

[xenou]

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.

 

[Maggie Barr]

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

 

[xenou]

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.