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

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 tbl2011Tier1, tblSTNameMstr, tblProdDescLU, tblSales;

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

Answer 2

Hi,

Can you post your solution?  I'm having the same problem.

Thanks,

Answer 3

found a way to make things work. will post solution later.

Answer 4

I am having similar trouble. i have a table with a list of parts in products. this list of parts is pretty large, but i dont think it would be causing this trouble.

here is the SQL:

qryPartReplacementToolList

SELECT qryProdSitePartToolFilterList.SerialNumber, qryProdSitePartToolFilterList.ProductID, tblProductPartList.PartListId, tblProductPartList.IMWPartNumberID, dbo_PART.DESCRIPTION, tblProductPartList.QTY, tblProductPartList.OneKMaint, tblProductPartList.FiveKRebuild, tblProductPartList.TenKRebuild, tblProductPartList.FtKRebuild, tblProductPartList.TwKRebuild, tblProductPartList.TwFKThouRebuild, tblProductPartList.ThKRebuild, tblProductPartList.ThFKRebuild, tblProductPartList.FtyKRebuild

FROM qryDontAddPartsToThese RIGHT JOIN (qryProdSitePartToolFilterList LEFT JOIN (tblProductPartList LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID = dbo_PART.ID) ON qryProdSitePartToolFilterList.ProductID = tblProductPartList.ProductID) ON qryDontAddPartsToThese.ProductID = tblProductPartList.ProductID

WHERE (((tblProductPartList.IMWPartNumberID) Like [Forms]![frmProductPartReplacementTool]![txtIMWPartFilter]) AND ((qryDontAddPartsToThese.ProductID) Is Null))

ORDER BY qryProdSitePartToolFilterList.ProductID, tblProductPartList.IMWPartNumberID;

i have been over this backwards and forwards. i have looked through the sub queries i dont see where the trouble is.

here is sub query SQL:

qryProdSiteToolFilterList

SELECT tblProductList.ProductID, tblProductList.SerialNumber, tblProductList.ProductTypeID, qryCompConfiguration.CompressorType, qryCompConfiguration.CBBANumber, tblCustomerList.IMWCustomerID, tblCustomerList.CompanyName, tblSiteInformation.SiteCommonName, subtblCountry.Country, tblServiceReps.Name AS ServRep

FROM tblServiceReps RIGHT JOIN (subtblCountry RIGHT JOIN ((tblSiteInformation LEFT JOIN tblCustomerList ON tblSiteInformation.CustomerListID = tblCustomerList.CustomerListID) RIGHT JOIN (qryCompConfiguration RIGHT JOIN tblProductList ON (qryCompConfiguration.ProductTypeID = tblProductList.ProductTypeID) AND (qryCompConfiguration.ProductID = tblProductList.ProductID)) ON tblSiteInformation.SiteID = tblProductList.SiteID) ON subtblCountry.CountryID = tblSiteInformation.CountryID) ON tblServiceReps.ServiceRepID = tblSiteInformation.ServiceRepID

WHERE (((tblProductList.SerialNumber) Like [Forms]![frmProductPartReplacementTool]![txtSerialFilter] & "*") AND ((tblProductList.ProductTypeID) Like [Forms]![frmProductPartReplacementTool]![cbProductType]) AND ((subtblCountry.Country) Like [Forms]![frmProductPartReplacementTool]![cbCountry]) AND ((tblServiceReps.Name) Like [Forms]![frmProductPartReplacementTool]![cbServRep]))

ORDER BY tblProductList.SerialNumber;

qryCompConfiguration

SELECT subdatatblCompressor.ProductID, subdatatblCompressor.ProductTypeID, tblCBBANumbers.CBBANumber, subtblCompressorType.CompressorType

FROM tblCBBANumbers RIGHT JOIN (subtblCompressorType RIGHT JOIN subdatatblCompressor ON subtblCompressorType.CompressorTypeID = subdatatblCompressor.CompressorTypeID) ON tblCBBANumbers.CBBAID = subdatatblCompressor.CBAEMID

WHERE (((tblCBBANumbers.CBBANumber) Like [Forms]![frmProductPartReplacementTool]![cbCBA]) AND ((subtblCompressorType.CompressorType) Like [Forms]![frmProductPartReplacementTool]![cbCompType]));

qryDontAddPartsToThese

SELECT tblProductPartList.ProductID, tblProductPartList.IMWPartNumberID

FROM tblProductPartList

WHERE (((tblProductPartList.IMWPartNumberID) Like [Forms]![frmProductPartReplacementTool]![txtIMWPartToAdd]));

if anyone has any ideas or you see something wrong with my sql please post.

Answer 5

Given the error message you're getting, you might do a Compact of your database before you run the query.

How large is the database? How much free space do you have on the drive pointed to by your TMP and TEMP environment variables?

---

Doug Steele, Microsoft Access MVP

http://www.AccessMVP.com/djsteele (no e-mails, please!)