none
System Resource Exceeded RRS feed

  • Question

  • Good morning, 

    Does anyone have the fix below to Office 2016 ?

    https://support.microsoft.com/en-us/kb/2726928

    Or knows another way to solve this problem

    Thanks in advance

    "System Resource Exceeded" error message when you perform a query in Access 2010

    Friday, January 8, 2016 10:06 AM

All replies

  • Depending on what your setup is (I can't see your SQL in the post)...

    1. Have you tried to find the cause by reducing the query to a simpler design to see if it is the amount of data being called?
    2. Have you tried to use a Function vs. a Query?
    3. If it really is a system resources error, do you have enough resources available on your PC to run the query. Have you checked the system resources to verify they are being reported correctly?
    4. You referenced the kb article (https://support.microsoft.com/en-us/kb/2726928) did you apply any of the fixes listed in that article  (https://support.microsoft.com/en-us/kb/2760394)?

    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, January 8, 2016 2:48 PM
  • KCDW

    I brought this question from the Brazil forum , I will return your suggestions to the user

    thanks

    Monday, January 11, 2016 10:01 AM
  • Infortunately, none of the tips solved the problem

    Its a simple query and there are  resources enough in the pc

    I should add also that it is an intermittent problem

    Monday, January 11, 2016 5:42 PM
  • Still without seeing the SQL or Function??? Is the query run from a command button or other function??? Are there lookup fields in the sql???

    if no to the above, Then it might be corruption. Have they tried Compact & Repair?

    Are they willing to show the code/sql that is in question?


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Monday, January 11, 2016 7:24 PM
  • Compact and repair OK


    Here is the code 

    Insert Into Beneficiario([idApolice],[idEmpresa],[idMatriculaEmpresa],[idMatriculaEmpresaSeq],[idCartao],[idPlano],[Nome],[dtNascto],[Sexo]) Values (7665,08658237,00024351,0201,'08658237000769306',251061,'LETICIA DE LIMA MORAIS',#11/22/2004#,'F')

    Update Beneficiario Set [idApolice]=7665,[idEmpresa]=08658237,[idMatriculaEmpresa]=00024351,[idMatriculaEmpresaSeq]=0100,[idCartao]='08658237000769012',[idPlano]=251061,[Nome]='CORNELIA DE LIMA RIBEIRO MORAIS',[dtNascto]=#09/29/1980#,[Sexo]='F'  Where ID=52210

    Tuesday, January 12, 2016 9:58 AM
  • Hi Andre,

    How did you get this error, with running this sql code in query design view or run with vba code? How many records in Beneficiario.

    I suggest you run your sql in query design view to check the result. Also, I suggest you run the inert query or update query one by one to check which caused this issue.

    I suggest you try insert and update with other tables to check whether this issue still exist.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, January 14, 2016 9:04 AM
  • First thanks for the help.

    The error happens when I run the code via VBA, and the insert is made record by record, every data line runs the insert code or updated code.

    As the post also tested in other tables and also the error persisted.

    The problem in my view is the management of the Office memory, up why I'm feeling a certain slowness in some solutions that had already developed earlier, I think you should have a problem with memory management.
    Thursday, January 14, 2016 5:29 PM
  • Hi Venildo,

    This thread is posted by Andre, did you have issue like Andre or you and Andre post the same issue?

    >> The error happens when I run the code via VBA, and the insert is made record by record, every data line runs the insert code or updated code.

    Could you share us the vba code? If you run the insert sql query in query design view, will this issue happen?

    I suggest you create a new table and test this vba code to check whether this issue still exist.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, January 15, 2016 3:07 AM
  • Hi, good morning

    I created new database, created new tables empty, but the problem continue. 

    I don't understand.

    Thank you

    Friday, January 15, 2016 8:42 AM
  • @Edward Z,

    This issue is happening with Venildo, I brought from MSDN BRAZIL trying to help him.

    @Venildo

    Try to run this query in the design view, as Edward say.


    André Santo | basevba.wordpress.com

    Friday, January 15, 2016 10:00 AM
  • When in time of designer gives no mistake, the problem is then that sometimes runs the command, that is, after you insert or update a record 20,000 the problem appears
    Friday, January 15, 2016 10:23 AM
  • If you are running the code item by item in VBA, we need to see the VBA code -- not necessarily the SQL statements themselves -- to know whether there is something I the code that constitutes a resource leak.  It may be some fault in the DAO or Access code over which you have no control, but it could be something in your own code that is consuming resources and not releasing them.  Please post the full VBA code that is involved in the problem.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    Sunday, January 17, 2016 2:08 PM
  • Good morning

    Above is the code involved int the problem

                    With oSql
                        .Tabela = "Beneficiario"
                        If tb.RecordCount = 0 Then
                            .TipoSql = tuTipoSql_Insert
                        Else
                            .TipoSql = tuTipoSql_Update
                            .Condicao = "ID=" & tb!Id
                        End If
                        .Valores "Plano", Trim(Mid(oImport.RetornaValor("Plano"), InStr(oImport.RetornaValor("Plano"), " "))), tuTipoCampo_String
                        .Valores "idMatricula", Mid(oImport.RetornaValor("idMatricula"), 9, 8), tuTipoCampo_Numerico
                    End With
                    Select Case oImport.RetornaValor("idApolice")
                        Case 7667, 7668

                            oDBPoa.Execute oSql.Sql
                        Case Else 
                            oDB.Execute oSql.Sql
                    End Select

    The SQL object is a class creates the SQL string, the ".Valores" method receives as parameter the field, the value to be filled and the field type.
    In short the only object creates the string sql

    The end result is

    Insert Into Beneficiario([idApolice],[idEmpresa],[idMatriculaEmpresa],[idMatriculaEmpresaSeq],[idCartao],[idPlano],[Nome],[dtNascto],[Sexo]) Values (7665,08658237,00024351,0201,'08658237000769306',251061,'LETICIA DE LIMA MORAIS',#11/22/2004#,'F')

    Update Beneficiario Set [idApolice]=7665,[idEmpresa]=08658237,[idMatriculaEmpresa]=00024351,[idMatriculaEmpresaSeq]=0100,[idCartao]='08658237000769012',[idPlano]=251061,[Nome]='CORNELIA DE LIMA RIBEIRO MORAIS',[dtNascto]=#09/29/1980#,[Sexo]='F'  Where ID=52210

     
    Monday, January 18, 2016 9:10 AM
  • Am I right in assuming that oDB and oDBPoa are DAO.Database objects?  What is oImport?

    Without seeing the code for the oSql class, it's hard to say whether there's a resource leak in that class.  It sounds simple enough, and at least you don't appear to be creating the oSql object repeatedly, but it's conceivable that there's something in that class that is allocating memory and not releasing it, or it's not being garbage-collected fast enough, so that in a tight loop you run out of resources.

    But it's also possible that the fault is actually in the DAO .Execute method.  However, in a test I was able to execute 30,000 simple INSERT statements with no error. So I would first look at the code for the oSql class, maybe at whatever class is behind the oImport object, and at everything else that is being executed in the loop.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, January 18, 2016 4:14 PM
  • I believe one also may get this error message in some cases when a (sub) query - which is the record source for a VBA task - has no records. 

    I am a little unsure on the specifics as it has been awhile and I don't recall exactly what VBA code I was implementing - but at some point during development I received a similar type message and it was because a query that was key to the VBA code had no records.....  The query itself was not particularly complex.

    The other time I have definitely seen this is when there were ton's of forms/reports open - and many which relied on each other in terms of referring to fields in other open objects (forms/reports)....and it was a 'straw that breaks the camel's back' situation.  I found if I closed some forms/reports that didn't need to be opened that everything worked ok.

    hope this helps....

    Monday, January 18, 2016 7:12 PM
  • The problem is not in the Import object nor the osql, these objects to use in other applications on other machines are all working, are simple classes, open the connection to the database and the other creates the sql strings, has no loop internal.
    The problem really started occur when had the update.
    I will continue to seek a solution.
    Thanks a lot for the help
    Monday, January 18, 2016 10:35 PM
  • Hi Venildo,

    I am not how did you implement oImport or osql. To narrow down this issue, I suggest you try the steps below:

    1. Replace osql.sql with real sql string, and run the code
    2. Use DAO to update data instead of your own classes.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, January 19, 2016 7:42 AM
  • The problem is not in the Import object nor the osql, these objects to use in other applications on other machines are all working, are simple classes, open the connection to the database and the other creates the sql strings, has no loop internal.

    I am not saying that these classes are necessarily at fault, but the fact that they work in other applications doesn't necessarily mean that they are not at fault.  If one of them has a resource leak, you wouldn't necessarily see a problem unless the relevant code was executed many times, in a loop.  I understand that the classes don't have an internal loop, but in this current case they appear to be used inside a loop in the calling code.  That could potentially cause you to run out of whatever resource is at stake.

    The problem really started occur when had the update.

    Which update?  You cited a KB article describing a fix for a problem in Access 2010, dating back to 2012, but then asked about Access 2016.  If a recent update to Access 2016 did cause the problem, it would help to know what specific update that was.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, January 19, 2016 6:15 PM
  • The problem started when I did the upgrade to office in 2016, previously had no problem.
    Tuesday, January 19, 2016 11:34 PM
  • Hi Venildo,

    Have you tried my above suggestion?

    >> The problem started when I did the upgrade to office in 2016, previously had no problem

    If you assume it is related with update, I suggest you test your application with office 2013 and office 2016 in different pcs to check whether this issue still exist.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, January 20, 2016 6:07 AM
  • @Edward Z

    Venildo has reported at MSDN Brazil that in the machine with WIN7 and Office2013 works with no problems !


    André Santo | basevba.wordpress.com

    Wednesday, January 20, 2016 9:52 AM
  • The problem started when I did the upgrade to office in 2016, previously had no problem.

    This doesn't make sense to me. What upgrade? Access 2016 is virtually the same as Access 2013 with the exception of available color schemes.

    1. Do you mean you installed Access 2016 and when you opened the database it stopped working?
    2. Have you checked for missing references since you made the change in Access?
    3. Have you verified any code you call is pointing to the correct Access folder?
    4. Have you checked to ensure you fully UNINSTALLED earlier versions of Access?
    5. Have you uninstalled Access 2016 and reinstalled the earlier Access version it worked with to verify it functions correctly now?
    6. Were there any other changes to your PC besides installing the new version of Access?

    We can only take shots in the dark unless you can be more forthcoming with the information we need to offer assistance. Please list your code or place a link to a dummy file you create for testing we can download to verify your findings or find a solution. I have not been able to reproduce the issue here based on the information you provided so far.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, January 20, 2016 2:21 PM
  • I formatted the PC and installed all over again, the machine was clean, without any information, that the micro with win7 in the micro to the win10, only had antivirus installed and more.
    For this reason I am finding it very strange that mistake.
    Wednesday, January 20, 2016 6:44 PM
  • Hi Venildo,

    Since we could not reproduce your issue at our side, so it would be better if you could share us the result we suggest you to test. For your issue, I think it is related with windows, Office and your own class which run sql statement. To check whether it is related with your own class, I suggest you use DAO instead of your own class to run sql statement.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, January 22, 2016 9:13 AM
  • Thanks for the help, I will try other alternatives if it works I will put the solution in the forum.
    Friday, January 22, 2016 8:04 PM
  • Problem solved.

    There was no empty space in the hard disk. LoL


    André Santo | basevba.wordpress.com

    Friday, November 25, 2016 3:51 PM