none
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

    Monday, August 06, 2012 8:38 AM

Answers

  • 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.

    • Marked as answer by tsluu Friday, August 24, 2012 12:13 AM
    Thursday, August 23, 2012 9:03 PM
    Moderator

All replies

  • 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.)

    Monday, August 06, 2012 8:49 AM
  • 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.

    Monday, August 06, 2012 8:58 AM
  • 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.)

    Monday, August 06, 2012 1:17 PM
  • 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


    Monday, August 06, 2012 2:18 PM
  • Thank you — your recommendation is doubly useful since it comes from someone I know is security-conscious.

    Matthew Slyman M.A. (Camb.)

    Monday, August 06, 2012 5:07 PM
  • 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

    Monday, August 06, 2012 7:10 PM
  • 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.

    Thursday, August 09, 2012 12:21 AM
  • 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

    Thursday, August 09, 2012 9:21 PM
  • Thanks Bill.

    Any other ideas you can think of?

    Friday, August 10, 2012 12:11 AM
  • 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

    Friday, August 10, 2012 4:52 AM
  • Hi Chris,

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

    Friday, August 10, 2012 5:45 AM
  • 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



    Monday, August 13, 2012 12:47 PM
    Answerer
  • 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

    Monday, August 13, 2012 2:25 PM
  • 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

    Tuesday, August 14, 2012 1:43 PM
    Answerer
  • 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.

    Sunday, August 19, 2012 11:20 PM
  • 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

    Monday, August 20, 2012 12:19 PM
    Answerer
  • Hi Dennis,

    I've tried your suggested steps. Still failed.

    Thanks for the effort.

    regards,

    Oliver

    Tuesday, August 21, 2012 8:08 AM
  • 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.

    Wednesday, August 22, 2012 1:41 PM
    Moderator
  • 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

    Thursday, August 23, 2012 12:39 AM
  • 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.

    • Marked as answer by tsluu Friday, August 24, 2012 12:13 AM
    Thursday, August 23, 2012 9:03 PM
    Moderator
  • 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

    Friday, February 01, 2013 3:23 PM
  • 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

    Friday, February 01, 2013 10:16 PM