Getting a query cannot be completed message when attempting to compile my database to an MDE

If you only have 36 MB of free space on your hard drive, it is time to:

1) clean out your recycle bin
2) get additional hard drive storage.
3) identify which query is causing the problem and try to rewrite it so that it doesn't return as many records.  I'm guessing you are trying something like:

SELECT A.*, B.*
FROM A, B
WHERE A.linkingField = B.Linking field
AND SomeOtherCriteria

This creates a cartesian join (for every record in A, you have every record in B).  so if A has 10 record and B has 10 records, it creates a 100 record recordset, and then filters out the records that don't meet your criteria.

You can rewrite this as:

SELECT A.*, B.*
FROM A INNER JOIN B on A.linkingField = B.Linking field
WHERE SomeOtherCriteria

This will generate a much smaller recordset

HTH
Dale

I am guessing that you have 36GB free...if you have 36 MB you have to increase your free space NOW...no matter what...Windows allocate HDD space dynamically so now you have 36 ,,,in the next second 0 in the next next second 100mb and goes on.
As for your problem...my bet is that something is wrong in the whole setup....at first i would try to decompile the application to see if you get some hidden "easter egg"...aka bug.
If not break down your query to as many subqueries as you can until you hit the brick wall.

<<
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.
>>

 You are running into a limit built into Access and your only "fix" is to re-work the process so it executes differently.

Jim.

Guys... John was correct... I have 36GB free.. not MB...

I may not have made my problem clear...  please stay with me...
All is fine in the MDB... when I attempt to create an MDE I click SAVE AS... select the MDE option.. and click..
I have no idea what query is running... its a save as operation.... so I can't rewrite any queries..

I'm pretty sure this is the normal process to compile..???

Decompile the application and check the autoexec macro if you have one or the code behind "Display Form"

I don't think it's actually executing a query, but trying to compile the execution plan.  Again, like "out of system resources", "The query cannot be completed" may be being thrown up as a general catch-all error message when a query doesn't succeed in someway.

Part of making a MDE is to compact and repair the db.  Part of a C&R is to reset statistics and to invalidate the costing plan for every query.     The query is supposed to re-cost at first run, but that may have been changed (and something rings a bell there - I believe it was changed and it re-costs during a C&R now).

When a query "complies" (that is come up with a new costing plan), originally it had to do that in a single 64k memory segment.   Microsoft worked on that and it is no longer limited to 64k, but what the limit is is un-documented.   it doesn't appear to be much higher though.

I believe this is what you are now running into.

Again, I had asked for you to supply the queries involved that were getting "out of system resources" when you were still on 32 bit because this is one of the limits you can run into.  And I don't believe the 64 bit version would have any impact on this limit as it has more to do with the basic architecture of Access.

 One quick way around this is to alias table names, which can drastically shorten the SQL statement that needs to be "compiled".

 I realize you are under enormous pressure to get this resolved, but as I mentioned in a PM, I think you are chasing your tail on this with trying all the different lock settings, moving to 64 bit, etc. and just moving from one problem to the next without addressing the root problem, which is the queries you have defined and the process(es) that you are trying to execute.

 Somehow (and maybe in several ways revolving around both design and amount of data), they are to close to the limits that Access has and adjusting lock settings and moving to 64 bit only solves some of those problems.

 You need to get at the root of this and not keep treating the symptoms.

Jim.

@John @Jim
I did the recompile today and am testing it now.

I will do a careful job of documenting errors form here on... including which query has caused them.. when possible to determine...

In the past, I can confidently tell you it has not been the complexity of the query.. certainly in some cases.. but sometimes it would haven while querying a single table..  often with very SHORT sql statements.  The thing that has always been the common denominator is the size of the dataset to be returned.. very large.

I will find some queries to share... @Jim.... I'm sure I sent you one.. I don't remember whether it was an EE upload or a PM.. Once I get through this crisis... I'll find it and send it again... still trying to get my code working properly with Access 64-bit.  Thanks