Access 2007 export error msg

Question

Access 2007 export error msg: The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2GB) or there is not enough temporary storage space on the disk to store the query result.

I have a query that is linked to 4 sql server tables, which results in 76,000 records with 79 fields in each record.  Running the query produces the correct result; however, exporting the results to a .csv file gives me the above error.  The export worked last week, and produced a text file with 45,380 MB.

Answer 1

Hmm. How big is the entire database (and have you compacted it recently)? How large are the two tables involved? Approximately what cardinality do you expect? (i.e.: on average, how many rows in SDS_BMS_SERVICE_COMMENTS do you expect to find for each row in qryPMsOnSchedule3? How often do you not expect to have a match?)

Answer 2

SELECT qryPMsOnSchedule3.Loc, qryPMsOnSchedule3.[ORD TYPE], qryPMsOnSchedule3.SUBTYPE, qryPMsOnSchedule3.[REF NUM], qryPMsOnSchedule3.[INV DATE], qryPMsOnSchedule3.COMMENT, SDS_BMS_SERVICE_COMMENTS.SERVICE_COMMENT, qryPMsOnSchedule3.[SCHED DATE], qryPMsOnSchedule3.[LAST LABOR]

FROM qryPMsOnSchedule3 LEFT JOIN SDS_BMS_SERVICE_COMMENTS ON (qryPMsOnSchedule3.CO=SDS_BMS_SERVICE_COMMENTS.COMPANY) AND (qryPMsOnSchedule3.DIV=SDS_BMS_SERVICE_COMMENTS.DIVISION) AND (qryPMsOnSchedule3.Loc=SDS_BMS_SERVICE_COMMENTS.LOCATION) AND (qryPMsOnSchedule3.[REF NUM]=SDS_BMS_SERVICE_COMMENTS.ORDER_REFERENCE) AND (qryPMsOnSchedule3.[ORD TYPE]=SDS_BMS_SERVICE_COMMENTS.ORDER_SOURCE_TYPE) AND (qryPMsOnSchedule3.COMMENT=SDS_BMS_SERVICE_COMMENTS.COMMENT_TYPE);

Answer 3

That can't be the entire query: it doesn't have a FROM clause!

Answer 4

am getting the same error message when i try to run a query : "The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2GB) or there is not enough temporary storage space on the disk to store the query result"

 There are a total of four querys in the file but the forth one is the only query that gives me the error message.

The SQL code is:

SELECT qryPMsOnSchedule3.Loc, qryPMsOnSchedule3.[ORD TYPE], qryPMsOnSchedule3.SUBTYPE, qryPMsOnSchedule3.[REF NUM], qryPMsOnSchedule3.[INV DATE], qryPMsOnSchedule3.COMMENT, SDS_BMS_SERVICE_COMMENTS.

Answer 5

Same problem with a much smaller DB.  Here's my code:

SELECT tblSales.ParentCustID, tblSales.ParentCust, tblSales.ChildCustID, tblSales.ChildCust, tblSales.ChildLocation, tblSales.ChildState, tblSales.ProdNbr, tblProdDescLU.SKU, tblSTNameMstr.State, tblProdDescLU.Description, tblProdDescLU.UOM, tblSTNameMstr.[2011QTY], tblSales.[Total 2011], tblSTNameMstr.[2010QTY], tblSales.[Total 2010], tbl2011Tier1.Tier1Price

FROM ((tblSTNameMstr INNER JOIN tblProdDescLU ON tblSTNameMstr.ProdNbr = tblProdDescLU.ProdNbr) INNER JOIN tblSales ON (tblProdDescLU.ProdNbr = tblSales.ProdNbr) AND (tblSTNameMstr.ProdNbr = tblSales.ProdNbr)) INNER JOIN tbl2011Tier1 ON tblSTNameMstr.ProdNbr = tbl2011Tier1.ProdNbr;

 

Appreciate answers (other than the obvious, moving up to SQL).