system resource exceeded

Question

I'm running MS Access 2010 on Windows 7 Enterprise SP1 (64 bit) with a 4 GB RAM machine.

I'm getting  a 'system resource exceeded' message when running the script below.

Not all machines get that errors. Only certain ones. They all have the same specs.

SELECT tblEmployee.last_Name, tblEmployee.first_Name, tblEmployee.Employee_Number, '' AS Description, tblWorkPack.cost_Code, tblWorkPack.cost_Type, tblWorkPack.area, tblWorkPack.workPack_Shortcut_Code, tblWorkPack.description, tblWorkPack.work_Pack_Code, tblWorkPack.client_Code_01, tblWorkPack.QMR_Code, tblWorkPackGroup.group_Description, tblWorkPackDiscipline.discipline_Description, tblEmployeeData.Work_Date, tblEmployeeData.week_Ending_Date, Sum(tblEmployeeData.hour_Worked) AS Total, tblEntityType.entity_Name, tblSubcontractorCompany.Code, tblDayRuleShiftType.shiftType_Description FROM tblClassification RIGHT JOIN ( ( SELECT * From tblEmployeeHistory  WHERE  classification_basis_Id=12 ) as tblEmployeeHistory RIGHT JOIN (((((tblWorkPackGroup RIGHT JOIN (tblEmployeeData  LEFT JOIN  tblWorkPack  ON  tblEmployeeData.workPack_Id = tblWorkPack.WorkPack_Id)  ON  tblWorkPackGroup.WorkPack_Group_Id = tblWorkPack.group_Id)  LEFT JOIN  (tblEmployee  LEFT JOIN  (tblEntityGroup RIGHT JOIN (tblEntityT
ype  LEFT JOIN  tblEntityGroupItem  ON  tblEntityType.Entity_Type_Id = tblEntityGroupItem.Entity_Type_Id)  ON  tblEntityGroup.Entity_Group_Id = tblEntityGroupItem.Entity_Group_Id)  ON  tblEmployee.entity_Type_Id = tblEntityType.Entity_Type_Id)  ON  tblEmployeeData.employee_Number = tblEmployee.Employee_Number)  LEFT JOIN  tblWorkPackDiscipline  ON  tblWorkPack.discipline_Id = tblWorkPackDiscipline.WorkPack_Discipline_Id)  LEFT JOIN  tblSubcontractorCompany  ON  tblEmployee.subcontractor_Company_Id = tblSubcontractorCompany.Subcontractor_Company_Id)  LEFT JOIN  (tblDayRuleCode  LEFT JOIN  tblDayRuleShiftType  ON  tblDayRuleCode.shiftType_Id = tblDayRuleShiftType.DayRule_ShiftType_Id)  ON  tblEmployeeData.day_Rule = tblDayRuleCode.Day_Rule_Code)  ON  tblEmployeeHistory.Employee_Number = tblEmployeeData.employee_Number  and (tblEmployeeData.work_Date between tblEmployeeHistory.emp_Start_Date AND  tblEmployeeHistory.emp_End_Date) )  ON  tblClassification.Classification_Id = tblEmployeeHistory.classification_Id W
HERE (tblEmployeeData.work_Date Between #05/04/2012# And #08/04/2012# AND  tblEntityGroup.Entity_Group_ID=23 ) GROUP BY tblEmployee.last_Name, tblEmployee.first_Name, tblEmployee.Employee_Number, tblWorkPack.cost_Code, tblWorkPack.cost_Type, tblWorkPack.area, tblWorkPack.workPack_Shortcut_Code, tblWorkPack.description, tblWorkPack.work_Pack_Code, tblWorkPack.client_Code_01, tblWorkPack.QMR_Code, tblWorkPackGroup.group_Description, tblWorkPackDiscipline.discipline_Description, tblEmployeeData.Work_Date, tblEmployeeData.week_Ending_Date, tblEntityType.entity_Name, tblSubcontractorCompany.Code, tblDayRuleShiftType.shiftType_Description

Answer 1

I find that Access gives somewhat error spurious messages like this "There is not enough memory to display the ..." when really the problem is memory leaks in Access (once Access has been running for a while, and you've looked at various data sets/ run some large queries on lots of data lots of times etc., Access starts gobbling up all the system resources you can throw at it.) Work-around? Shut down Access, wait 10 seconds, reload your Access application. Sometimes it helps to restart Windows too...

I'm not at all sure if this is the answer to this specific problem...

---

Matthew Slyman M.A. (Camb.)

Answer 2

Hi Matthew,

Would it help if I add more RAM to the machines.

I'm using a machine with 12GB of RAM and it ran without any problems.

Answer 3

How many rows are there in the tables you are JOINing with this query?

How big are these rows, per record, in bytes?

Bear in mind I'm using a machine with 4GB to manipulate large databases, and having no problems.

Depending on the design of your query, the required memory might vary substantially with the number of rows in the tables you are JOINing (so that, it is quite possible that upgrading all your machines might only buy you a little time, whereas redesigning your query might fix this problem).

Is there another way to organise your query, so it takes less temporary storage space for MS Access to run it? As a general rule, I find that the best way to optimise queries of this sort is to

1. Write the sequence of JOINs so that the most constraining conditions (resulting in small numbers of records in intermediate result sets) are applied first,
2. Convert WHERE conditions into JOIN...ON conditions so they will be applied earlier.

Less urgently, have you considered porting the db back-end to SQL Server Express and running this as a PassThrough query (with the results only, being passed through to the front-end GUI?) SQL Server is much more intelligent than MS Access in optimising queries. The 2008 Version has a new "star join" optimiser, which might be ideal for this purpose.

Your query is not that easy to read: did you generate this with the MS Access query designer? Can you take a screenshot showing the design of the query in a graphical format?

---

Matthew Slyman M.A. (Camb.)

Answer 4

Matthew

I'm surprised you don't have a SQL formatter. [smile]. I use the one at SQLinForm.com.

Does this help?

SELECT   tblEmployee.last_Name
         , tblEmployee.first_Name
         , tblEmployee.Employee_Number
         , '' AS Description
         , tblWorkPack.cost_Code
         , tblWorkPack.cost_Type
         , tblWorkPack.area
         , tblWorkPack.workPack_Shortcut_Code
         , tblWorkPack.description
         , tblWorkPack.work_Pack_Code
         , tblWorkPack.client_Code_01
         , tblWorkPack.QMR_Code
         , tblWorkPackGroup.group_Description
         , tblWorkPackDiscipline.discipline_Description
         , tblEmployeeData.Work_Date
         , tblEmployeeData.week_Ending_Date
         , SUM(tblEmployeeData.hour_Worked) AS Total
         , tblEntityType.entity_Name
         , tblSubcontractorCompany.Code
         , tblDayRuleShiftType.shiftType_Description
FROM     tblClassification
         RIGHT JOIN (
                  ( SELECT *
                  FROM    tblEmployeeHistory
                  WHERE   classification_basis_Id=12
                  )
                  AS tblEmployeeHistory
                  RIGHT JOIN (((((tblWorkPackGroup
                           RIGHT JOIN (tblEmployeeData
                                    LEFT JOIN tblWorkPack
                                    ON       tblEmployeeData.workPack_Id = tblWorkPack.WorkPack_Id)
                           ON       tblWorkPackGroup.WorkPack_Group_Id = tblWorkPack.group_Id)
                           LEFT JOIN (tblEmployee
                                    LEFT JOIN (tblEntityGroup
                                             RIGHT JOIN (tblEntityT ype
                                                      LEFT JOIN tblEntityGroupItem
                                                      ON       tblEntityType.Entity_Type_Id = tblEntityGroupItem.Entity_Type_Id)
                                             ON       tblEntityGroup.Entity_Group_Id = tblEntityGroupItem.Entity_Group_Id)
                                    ON       tblEmployee.entity_Type_Id = tblEntityType.Entity_Type_Id)
                           ON       tblEmployeeData.employee_Number = tblEmployee.Employee_Number)
                           LEFT JOIN tblWorkPackDiscipline
                           ON       tblWorkPack.discipline_Id = tblWorkPackDiscipline.WorkPack_Discipline_Id)
                           LEFT JOIN tblSubcontractorCompany
                           ON       tblEmployee.subcontractor_Company_Id = tblSubcontractorCompany.Subcontractor_Company_Id)
                           LEFT JOIN (tblDayRuleCode
                                    LEFT JOIN tblDayRuleShiftType
                                    ON       tblDayRuleCode.shiftType_Id = tblDayRuleShiftType.DayRule_ShiftType_Id)
                           ON       tblEmployeeData.day_Rule = tblDayRuleCode.Day_Rule_Code)
                  ON       tblEmployeeHistory.Employee_Number = tblEmployeeData.employee_Number
                  AND
                           (tblEmployeeData.work_Date BETWEEN tblEmployeeHistory.emp_Start_Date AND      tblEmployeeHistory.emp_End_Date
                           )
                  )
         ON       tblClassification.Classification_Id = tblEmployeeHistory.classification_Id W HERE (tblEmployeeData.work_Date BETWEEN #05/04/2012# AND      #08/04/2012#
         AND      tblEntityGroup.Entity_Group_ID=23 )
GROUP BY tblEmployee.last_Name
         , tblEmployee.first_Name
         , tblEmployee.Employee_Number
         , tblWorkPack.cost_Code
         , tblWorkPack.cost_Type
         , tblWorkPack.area
         , tblWorkPack.workPack_Shortcut_Code
         , tblWorkPack.description
         , tblWorkPack.work_Pack_Code
         , tblWorkPack.client_Code_01
         , tblWorkPack.QMR_Code
         , tblWorkPackGroup.group_Description
         , tblWorkPackDiscipline.discipline_Description
         , tblEmployeeData.Work_Date
         , tblEmployeeData.week_Ending_Date
         , tblEntityType.entity_Name
         , tblSubcontractorCompany.Code
         , tblDayRuleShiftType.shiftType_Description

---

Bill Mosca
http://www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 5

Thank you — your recommendation is doubly useful since it comes from someone I know is security-conscious.

---

Matthew Slyman M.A. (Camb.)

Answer 6

You're welcome, Matthew. I've been using the desktop version of SQLinForm for years. An excellent tool not only for Access but other engines as well. I think it is well worth the price for all the customizing available.

---

Bill Mosca
http://www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 7

I took some time to test the script on different machines and environments. Tried it on a laptop, on a desktop and a virtual machine. All with 4GB RAM and it all worked without any problems.

I'm just curious as to why it worked on some and not on some.

Answer 8

Possibly it failed because of other services running on some machines. The Task Manager might be useful in finding the cause. I know that whenever the virus scanner kicks in on my PC at work everything slows to a crawl.

---

Bill Mosca
http://www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 9

Thanks Bill.

Any other ideas you can think of?

Answer 10

Not sure where your Tables are but just to check, your Tables are on a server? and you are connecting through the UNC path and not mapped drives right?

---

Chris Ward

Answer 11

Hi Chris,

For my case, I imported all the tables from a shared database and then run the script locally.

Answer 12

Hi Guys,

I see some talk on memory in the thread and wanted to clarify that point. If these installations of Access are 32bit installations, you are limited to 2GB of user virtual memory. Unless you are using many applications at the same time, a typical machine should have a sufficient paging file to create the full 2GB of virtual memory available for the process but you could check the paging file size using this link.

Change the size of virtual memory

http://windows.microsoft.com/en-US/windows-vista/Change-the-size-of-virtual-memory

If your machine can create the 2GB of virtual memory in the paging file, it doesn't matter if the machine has 4GB or 12GB of physical memory available and adding more physical memory will not prevent the issue.

One way to know if you reaching/exceeding the virtual memory, is to use a tool called VMMap. VMMap allows you to attach to a running process and then provides a snapshot of the virtual memory of the application.

VMMap

http://technet.microsoft.com/en-us/sysinternals/dd535533

If the process is near the 2GB total (1.7-2), then you are likely exceeding the virtual memory available. You would either need to redesign the query to make it more efficient or install Access 2010 in 64bit which has a much larger virtual memory space to work with.

Remember that when working with VMMap it is taking snapshots. I typically launch Access, then launch VMMap and attach to the Access process. I recreate my scenario in Access and when I want the memory snapshot, I select View > Refresh in VMMap.

For this issue, I would be interested if each machine has the same version of the acecore.dll file. Acecore.dll should be located here: C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14.

Regards,

Dennis

Answer 13

I'm not sure about your information here. My page files are set to 1.5 times the Ram onboard. On my 4GB system I have the max pagefile set to 6GB. The 12 I also have set to 6 as I don't need any more. Both are 32 bit systems. If I did need more on the 4GB system, I would add a flashcard to gain more. 

---

Chris Ward

Answer 14

Hi Chris,

So unless you are using this entire 6GB page file, the msaccess.exe process will be able to consume up to 2GB. Therefore, adding more memory would not affect the situation.

Have you been able to look at the versions of acecore.dll between your 2 machines?

Regards,

Dennis

Answer 15

Hi guys,

Thanks for the feedback. I tried adjusting the virtual storage but the problem still come up. 

Could it be a dll that Access 2010 is using that is causing this problem.

Answer 16

Hi Chris,

I have mentioned the acecore.dll as a possible reason you would see a difference in behavior between the 2 machines. Acecore.dll should be located here: C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14. Please note the versions on your machines.

If the versions are the same, I would attempt to change the process affinity on the failing machine.

Steps:

1. Launch your database

2. Open task manager

3. Right-click on msaccess.exe from the process tab and choose Set Affinity

4. Uncheck the All option and select a single processor

5. Try to execute your query

Regards,

Dennis

Answer 17

Hi Dennis,

I've tried your suggested steps. Still failed.

Thanks for the effort.

regards,

Oliver

Answer 18

Hi Tsluu,

As Dennis mentioned, did you have a chance to check the version of the acecore.dll file on both machines?  If so, what version do you have installed on the working machine and non-working machine?

If you are using the same version of acecore.dll and going through the steps to change the affinity didn’t seem to make a difference, then my next recommendation would be to open up a support case with Microsoft so that we can look into the issue with you.

Online Assisted Support Options (create cases online) - http://support.microsoft.com/select/default.aspx?target=assistance

Best Regards,

Nathan O.

Microsoft Online Community Support

---

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 19

Hi Nathan,

I did check the versions of acecore.dll and it is the same. It is 14.0.4760.1000.

At the moment, we are getting I.T. to look into the configurations of the PCs. I suspect it lies with the hardware rather than Access itself.

I will put in a support case if need be. I'll just wait for the outcome from I.T.

Thanks.

regards,

Oliver

Answer 20

Hi Oliver,

Thanks for the update.  Based upon that build number for the acecore.dll file it looks like you also don’t have SP1 installed for Office 2010.  You may want to see if your IT department can fully update the Office 2010 suite so that at least SP1 for Office 2010 is installed.  Then after installing this, you could test the code to set the affinity again. Then if that doesn’t work and / or your IT department is unable to find anything wrong with the hardware, a support case would probably be the next best option.

Best Regards,

Nathan O.

Microsoft Online Community Support

---

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 21

iam using office 2013 x64 on win8 x64

RAM 4G

i have query

SELECT A.Nama, A.Alamat, A.Telp, A.Kota,'Tgl Pinjam : ' & P.TglPinjam AS JML
FROM Anggota AS A, Pinjam AS P WHERE A.ID = P.IDP
AND P.IDB=? AND P.Status='Hilang'

i think

its very simple query

but error is same

system resource exceeded

?????????

i so confused

Answer 22

iam using office 2013 x64 on win8 x64

RAM 4G

i have query

SELECT A.Nama, A.Alamat, A.Telp, A.Kota,'Tgl Pinjam : ' & P.TglPinjam AS JML
FROM Anggota AS A, Pinjam AS P WHERE A.ID = P.IDP
AND P.IDB=? AND P.Status='Hilang'

i think

its very simple query

but error is same

system resource exceeded

?????????

i so confused

What is this supposed to do?

WHERE A.ID = P.IDP 
AND P.IDB=? AND P.Status='Hilang'

That does not make sense. Is it just a typo here or is that your actual syntax?

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals