Link to home
Start Free TrialLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

What could be causing this message

What is causing this message?User generated image

Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

is your DB larger (or close to) 2GB?
Many things could cause that message:
  • A query that has no escape sequence stuck in an infinite loop
  • A DB larger than 2Gb
  • Disk space issues
  • Memory limitations
This message is causing either insufficient disk space or an incorrectly written query.
For example if you have two tables (TableA and TableB) each one with row size 500 B and with the total number of rows 2000 in each of them then the following query exceeds the 2 GB limit:
SELECT * FROM TableA, TableB

Open in new window

and it could be not only the missing join condition like in the above case but also incorrectly defined join condition etc.
If you have the SQL code of the query which is causing the message post it here and it will allow to tell more.
Microsoft Access has a 2 gigabytes, minus the space needed for system objects. Note:

 You can work around this size limitation by linking to tables in other Access databases. You can link to tables in multiple database files, each of which can be as large as 2GB.
Why leave us guessing? Post your query and some information.
Avatar of SteveL13


Here is the SQL:
SELECT DISTINCT qryMonthlyInvoices.InvoiceDate, qryMonthlyInvoices.InvoiceNumber, qryMonthlyInvoices.CompanyName, qryMonthlyInvoices.CompanyID, tblCompanies.Address1, tblCompanies.Address2, [City] & ", " & [State] & " " & [ZipCode] & " " & [Country] AS CSZC, tblCompanies.MainPhone, tblCompanies.OperationsEmail, qryInvoiceDetailsReport.DealID, qryInvoiceDetailsReport.Counterparty, qryInvoiceDetailsReport.TransactionDate, qryInvoiceDetailsReport.ContractN, qryInvoiceDetailsReport.Product, qryInvoiceDetailsReport.Trader, qryInvoiceDetailsReport.ContractTerm, qryInvoiceDetailsReport.Location, qryInvoiceDetailsReport.Volume, qryInvoiceDetailsReport.Price, qryInvoiceDetailsReport.CommRate, qryInvoiceDetailsReport.CommAmount, tblPaymentTerms.Description, tblPaymentTerms.DaysAdd
FROM ((qryMonthlyInvoices LEFT JOIN tblCompanies ON qryMonthlyInvoices.CompanyID = tblCompanies.CompanyID) INNER JOIN qryInvoiceDetailsReport ON qryMonthlyInvoices.InvoiceNumber = qryInvoiceDetailsReport.InvoiceNumber) LEFT JOIN tblPaymentTerms ON tblCompanies.PaymentTermsID = tblPaymentTerms.PaymentTermsID
ORDER BY qryMonthlyInvoices.InvoiceNumber;

Open in new window

You are selecting Invoice details together with some additional info without any date range specification. The output must contain redundant data but that's an intention probably.
Why did you use DISTINCT?

I would also expect the qryMonthlyInvoices and qryInvoiceDetailsReport are defined as queries.
Could you also post the query definitions and number of rows in tables involved in the query?
How many rows are in the qryInvoiceDetailsReport query? And how many rows are in the underlying table?

First of all try it without DISTINCT.
Then try some simpler query containing just columns from qryMonthlyInvoices and qryInvoiceDetailsReport. 
Got it with:
    Dim qdf As QueryDef
    Dim strReportName As String
    Dim strSQL As String

    Set qdf = CurrentDb.QueryDefs("qryInvoiceHeader")
    strReportName = "rptMonthlyInvoice"

    strSQL = qdf.SQL
    If InStrRev(strSQL, ";") > 0 Then strSQL = Left(strSQL, InStrRev(strSQL, ";") - 1)
    If InStrRev(strSQL, "WHERE ") > 0 Then strSQL = Left(strSQL, InStrRev(strSQL, "WHERE ") - 1)
    If InStrRev(strSQL, "ORDER ") > 0 Then strSQL = Left(strSQL, InStrRev(strSQL, "ORDER ") - 1)
    strSQL = strSQL & " WHERE qryMonthlyInvoices.InvoiceNumber = " & Me.cboSelectInvoiceN
    strSQL = strSQL & " ORDER BY qryMonthlyInvoices.InvoiceNumber"
    qdf.SQL = strSQL

'    DoCmd.OpenReport strReportName, acViewPreview, "", "InvoiceNumber = " & Me.cboSelectInvoiceN, acNormal
    DoCmd.OpenReport strReportName, acViewPreview, , , acNormal

    Set qdf = Nothing

Open in new window

It seems to be a query driving a report. If so, do remove all sorting from the query, as it will be ignored anyway, and specify the sorting in the report itself.
Hmmm... The above code belongs to the same database most likely but how is it related to the question?

Would it be possible to answer questions which should move us closer to the solution?
Gustav, is it always more efficient to let the report do the sorting?
It is more than that; it is the only reliable method. The trap is, that sorting in the query at first may seem to work, but then one day ...
So, at best, sorting in the query does no harm.
sorting in the query at first may seem to work, but then one day ...
Do you have any example please?
No, but it is the way it works.
So, make it a habit to sort in the report and stay safe.
OK, you don't have an example but could you confirm the invalid sorting in a query is specific Access feature or we may observe it in a query on table linked at the SQL Server or at the SQL Server directly?

Or in other words where did you observe the invalid sorting in a query?

Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
How did the answer marked as a solution helped to fix the error in the question?
I am just curious...