System resource exceeded

Question

I received this message while running a series of queries that are matching names and addresses. It has run successfully a hundred times but now suddenly is hanging up. The queries are on the front-end joining backend tables. I'm running Access 2010 in Office 2010 32-bit on a Win7 64-bit machine with 8GB RAM. I shut down every other program. My total physical memory usage was at 37%.

How can I manage my memory consumption better?

---

Darrell H Burns

Answer 1

 Do you have any custom procedures being used in the queries? 

---

Bob Larson, Access MVP 2008-2010, 2011

Answer 2

 Do you have any custom procedures being used in the queries? 

---

Bob Larson, Access MVP 2008-2010, 2011

No, but I do nest the queries. For example, I execute about 30 query combinations like this:

SELECT Replace(Replace(Replace(Replace(Replace(Nz([Loc_Street_Num]) & Nz([Loc_Street_PreDir]) & Nz([Loc_Street_Name]) & Nz([Loc_Street_PostDir]) & Nz([Loc_Street_Suffix]) & Nz([Loc_SubAddr_Num]) & Nz([Loc_Box_Num]) & Nz([Loc_Zip_Primary])," ",""),"-",""),"/",""),"*",""),"#","") AS AddressCompressed, Count(Location.Loc_ID) AS NumRecs, Min(lk_Data_Source.Merge_Priority) AS MinDSMergePriority
FROM lk_Data_Source INNER JOIN Location ON lk_Data_Source.Code = Location.PrimaryDataSource
WHERE (((Location.Loc_Street_Num)>"") AND ((Location.Loc_City)>"") AND ((Location.Loc_State)>"")) OR (((Location.Loc_City)>"") AND ((Location.Loc_State)>"") AND ((Location.Loc_Street_Name)>"")) OR (((Location.Loc_Street_Num)>"") AND ((Location.Loc_Zip_Primary)>"")) OR (((Location.Loc_Street_Name)>"") AND ((Location.Loc_Zip_Primary)>""))
GROUP BY Replace(Replace(Replace(Replace(Replace(Nz([Loc_Street_Num]) & Nz([Loc_Street_PreDir]) & Nz([Loc_Street_Name]) & Nz([Loc_Street_PostDir]) & Nz([Loc_Street_Suffix]) & Nz([Loc_SubAddr_Num]) & Nz([Loc_Box_Num]) & Nz([Loc_Zip_Primary])," ",""),"-",""),"/",""),"*",""),"#","")
HAVING (((Count(Location.Loc_ID))>1 Or (Count(Location.Loc_ID))>1 Or (Count(Location.Loc_ID))>1 Or (Count(Location.Loc_ID))>1));

is nested inside...

SELECT vw_Location_Dupes_MinDS_6.AddressCompressed, Min(Location.Loc_ID) AS FirstID
FROM (lk_Data_Source INNER JOIN vw_Location_Dupes_MinDS_6 ON lk_Data_Source.Merge_Priority = vw_Location_Dupes_MinDS_6.MinDSMergePriority) INNER JOIN Location ON lk_Data_Source.Code = Location.PrimaryDataSource
WHERE (((Replace(Replace(Replace(Replace(Replace(Nz([Loc_Street_Num]) & Nz([Loc_Street_PreDir]) & Nz([Loc_Street_Name]) & Nz([Loc_Street_PostDir]) & Nz([Loc_Street_Suffix]) & Nz([Loc_SubAddr_Num]) & Nz([Loc_Box_Num]) & Nz([Loc_Zip_Primary])," ",""),"-",""),"/",""),"*",""),"#",""))=[AddressCompressed]))
GROUP BY vw_Location_Dupes_MinDS_6.AddressCompressed;

is nested inside...

INSERT INTO Temp_Loc_Matches ( FromLocID, ToLocID, MatchLevel )
SELECT Location.Loc_ID AS FromLocID, vw_Location_Dupes_6.FirstID AS ToLocID, 6 AS MatchLevel
FROM Location, vw_Location_Dupes_6
WHERE (((Location.Loc_ID)<>[FirstID]) AND ((Replace(Replace(Replace(Replace(Replace(Nz([Loc_Street_Num]) & Nz([Loc_Street_PreDir]) & Nz([Loc_Street_Name]) & Nz([Loc_Street_PostDir]) & Nz([Loc_Street_Suffix]) & Nz([Loc_SubAddr_Num]) & Nz([Loc_Box_Num]) & Nz([Loc_Zip_Primary])," ",""),"-",""),"/",""),"*",""),"#",""))=[AddressCompressed]))
ORDER BY Location.Loc_ID;

---

Darrell H Burns

Answer 3

You're probably suffering from all of the Replace functions you are using.  If you have to use it so much I would just create your own function to replace the values so only one function call is necessary per record.

---

Bob Larson, Access MVP 2008-2010, 2011