Access Query won't run, getting error.

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
188
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top