MS Access 2016 - 'System Resource Exceeded' - Windows 10

Hi,

I have just started receiving the message 'System Resource Exceeded' when doing the most harmless of things in MS Access. Things like opening a table in design mode, saving a record or clicking on a drop-down.

Although intermittent, when it occurs it is solid and I have to restart MS Access; clearly highly undesirable as I am supporting my MS Access App in a 10-15 user environment.

I have read several posts elsewhere but none seem to offer a reasonable solution. Some suggest editing the registry, which does little to enthrall me especially as this offers no confirmed solution. Do you have knowledge of this and can help me please.

Many, many thanks in anticipation
Rayshka
RayshkaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The error you're getting could be caused by many different things. First, be sure that your installations of Office and Windows are fully up to date, and that you have applied any relevant hotfixes.

Next - is your database split into a backend (Tables only) and a Frontend (everything else)? If not, then the first order of business is to do that. Access has a splitter wizard, or you can do it yourself. After splitting, make copies of the FE and provide a copy to each user that needs it.

After splitting you may need to perform maintenance on your database. Make a backup of your database, then do this:

1. Compact the database
2. Compile your database - from the VBA Editor click debug - Compile. Fix any errors, and continue doing this until the menuitem is disabled.
3. Compact again.

You may also need to decompile your database. To do that, make a shortcut with this as the target:

"full path to msaccess.exe" /decompile

Run that, then select your database when Access opens. After if opens completely, run the 3 steps above again.

Finally, you may need to create a new, blank database and import everything to that new database.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
in addition to what Scott said, I would add that these can be notoriously difficult to track down.  They can be due to environment, the DB itself, or bugs in Access.

 Also I'd add to look for a pattern; are you working with data at the time (i.e. filtering), opening/closing a form, etc.

 in regards to records, if your dealing with large record sets, you can be running out of locks, which is one possible cause for that message as well.

 At the startup of the app, do:

 dbEngine.SetOption dbMaxLocksPerFile , 100000

 and see if that helps at all.

Jim.
McKnifeCommented:
Normal troubleshooting step 1 is to logon as a different user and try to reproduce it.
If it happens for that other user, too, you would do the same on another system with same OS and same access. If it works there, you'd uninstall and reinstall access.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

RayshkaAuthor Commented:
Hi Scott, Jim & McKnife,

I have still been receiving the message but have now:

1.  Compacted and repaired the database
2.  Rebuilt the database by creating an empty one and importing all the objects
3.  Included a statement setting the maximum locks per file to 100,000 - I have done this because some tables have a large number of records 50,000+
4.  Compiled the database
5.  Compacted and repaired the database again

I am also now recording the details of each occurrence to establish any trends, etc....

Thank you for your help. I'll let you know any outcomes in due course. Please advise if I need to do anything else....

Regards
Rayshka
McKnifeCommented:
"Please advise if I need to do anything else...." - did you do as I suggested? Your feedback didn't tell me.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Keep us updated and let us know if anything has changed.

Some other things to check as well:

1. Drop to a command prompt and type SET.   Check for TMP and TEMP environment variables, and if defined that they point to valid drives and directories

2. Clear those directories out and also:

C:\Temp
C:\Windows\System32\Temp

 If TMP or TEMP does not point to them.  

In all the temp directories, you can safely delete all files.  If a file is being used, you will get a lock conflict ("file in use") and you can just skip it.

 When JET performs certain operations (performing a large transaction), it may go to disk for temp storage.   Temp files show up as ~JET0000.TMP

 Hopefully between that and the other things mentioned, that will clear up the problem for you.

  I think to I will lobby Microsoft to somehow log what Access is doing at the time this error is encountered.

Many applications today write to the application event log to give an indication of what was going on internally when something happens.

Jim.
RayshkaAuthor Commented:
Sorry McKnife I didn't mention that I will be doing what you suggested later today (many balls in the air at present) and will let you know outcome. Thank you for your help......

Rayshka
RayshkaAuthor Commented:
Hi Everyone,

Thought an update is appropriate as the problem still persists. I have taken McKnife's advice et-al and have received the problem logged in as different users and running the app on different machines but with the same OS and Office version.

Here's a summary: The database is a split front end app and back end tables. It is a Customer Relations Management system with around 30,000 clients. The key tables that together form the heart of the system are:
Clients - 30,000 records
Notes - 51,000 (customer contact commentaries)
Diary - 37,000
Contacts - 32,000
The system has 8 permanently open forms, including a menu bar on each allowing the user to quickly navigate them, whilst processing sales through a structured approach.

The system has been in operation for about 8 years and I have recently updated it with the client's new set of requirements. The number of records it manages has not significantly changed over the period of the change.  The problem appears to have started when I added a new calendar/diary function. This function handles the diary and associated tables and its queries are both complex and resource hungry. However, the queries only run when the user updates the diary so for the majority of time the diary is in a read only mode.

I have attached a log of the 'System Resource Exceeded' events. The actions in the log were taken in addition to the actions noted in previously comments. I have attempted to categorise each logged event to try and ascertain a trend. As a result the problem appears to relate to manipulating records - creating sets and saving records.

I'd appreciate any other suggestions. I have not tried to break-out the Calendar/Diary functions to go back to as things were prior to the problem as this will result in some requirements not being met. However, I guess this still remains an option if all else fails!

Jim, have you had any luck with Microsoft?

Thanks everyone for your help....

Regards
Rayshka (Ray)
RayshkaAuthor Commented:
Hi Everyone,

Just to follow-up previous comment, I am now persistently getting 'Cannot open any more databases'. These have occurred: Open Form; Save Record; Run DSum function.

Regards
Ray
RayshkaAuthor Commented:
Hi Jim Dettman, Scott, KcKnife,

Jim have you had any feedback from lobbying MS?

Everyone, the pressure is on me to get this fixed by my clients. The version affected (latest) is urgently wanted but I obviously do not want to expose them to any kind of problem.

I believe I have tried everything you all have suggested, which has been very helpful especially in understanding more about what might cause the problem! Now I'm desperate to get a fix and anything, anything you could come up with would be most welcome.

Thanks guys, appreciate the difficulties!

Rayshka
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Hi Rayshka,

I believe Jim meant that he'd lobby MSFT to add that feature at some point. It's not going to help you now.

Sounds like you've determined the issue came about when you added the diary functionality, so it would seem that would be the place to begin the troubleshooting.

Can you explain the Diary/Calendar functions that seem to have caused this?
RayshkaAuthor Commented:
Hi Scott,

Thanks for getting back to me - good to know that I have a helper with this......

The Calendar/Diary is based on actions and events totalling 40,000+. Each of the days in a month are queried for 'Open' records for the days in the displayed month. The Years and months are displayed by selection (Year and Jan-Dec). When selected the diary records for the year/month are queried for each of the days in the selected month. Once queried the records are displayed until the date is changed by the user.

If any event in the month displayed is clicked the corresponding week is displayed in another tab and the event's details displayed in a daily window.

If there is no activity as described above then there is no querying of records and displays are static. If a new event is added, the records are re-queried automatically.

Hope this helps....

Regards
Rayshka
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I believe Jim meant that he'd lobby MSFT to add that feature at some point. It's not going to help you now.>>

 Scott is correct and I'm sorry if I gave you the wrong impression.  Lobbying Microsoft to make a change in this area would take many months at the very least.

<<Sounds like you've determined the issue came about when you added the diary functionality, so it would seem that would be the place to begin the troubleshooting. >>

  When you bump into this, after checking off the usual list of suspects, the only recourse is to refactor the process in some way.

 If you can post a DB with the form(s) or report(s) and the queries involved, we may be able to make some suggestions.

 But right off, one thing to look for is:

 SELECT * FROM <some table>

 You want to select only the fields you need for an operation.  Doing so will limit the number of tablesID's in use (each table that is open and field carries a table reference ID).  Access has an internal limit that floats around 2,048.  "Too many databases" is also somewhat tied to that.

Jim.
RayshkaAuthor Commented:
Hi Jim,

Thanks for the reply I have counted the SELECT * sqls and there are 10 so I have modified them to only select the required fields.

Regarding the open tables IDs issue, please answer me a few questions:

Does setting an open recordset - Set rst=dbs.OpenRecordSet("....") - add to the number of tablesIDs?
Does - Set rst = frm.RecordsetClone - also add to the number of TablesIDs?
By coding - rst.close and then Set rst=nothing - deduct from the number of TablesIDs?
Additionally how to keep the number of TablesIDs to a minimum?
I use a lot of SQL in the Diary functions to set object rowsources; sql="SELECT ........" and then someobject.RowSource=sql. Can these be some of the culprits?

I ask all this as maybe not all my open recordsets are closed properly and it would be good to know how this works in practice and help resolve this problem.

Sorry for burdening you with all this but I am desperate to get a satisfactory solution. If as you say the open tables could contribute to the problem, I want to make my code squeaky clean in this respect.

Best regards
Ray
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Does setting an open recordset - Set rst=dbs.OpenRecordSet("....") - add to the number of tablesIDs?>>

 Yes.

<<Does - Set rst = frm.RecordsetClone - also add to the number of TablesIDs?>>

 Yes.

<<By coding - rst.close and then Set rst=nothing - deduct from the number of TablesIDs? >>

 Yes.

<<Additionally how to keep the number of TablesIDs to a minimum? >>

 Don't use SELECT * and close things where your done with them.

<<I use a lot of SQL in the Diary functions to set object rowsources; sql="SELECT ........" and then someobject.RowSource=sql. Can these be some of the culprits? >>

   Not really.   The only one that counts is the .RowSource   Just setting a string doesn't do anything.   But when you set that rowsource, then every field you reference carries a table ID.

 When you reset it, all the old references go away and the new references now count.

 It's like you closing and opening a recordset in code.

Jim.
RayshkaAuthor Commented:
Hi Jim,

OK thanks for your feedback. This gives me a good idea as to why I am getting this error. The number of .RowSource settings in the Diary are large so I am going take this out of the main app and set the Diary as a separate app. Only issue there is I haven't found a way in the past of jumping out of the Diary direct to a client in the main app, which was the reason I consolidated the Diary into the app in the first place.

Will let you know the outcome. Once again thanks for your help....
Rayshka
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
If you let us have a look at what you've done, we may be able to make some suggestions on changes.

But certainly splitting off part of the app would help.

Jim.
RayshkaAuthor Commented:
Hi Jim, et-al,

I have split out the diary and all seems to be OK, or at least I haven't as yet received the error message. It sounds most plausible your view that the problem is the number of open recordsets because of the number of row sources set in the diary. I have attached a notepad copy of the Diary functions code and when you look at the functions 'List_Weekly_Events' and 'Format_Daily_Lists' the above is quite evident. Any suggestions would be most welcome, including how to jump from the Diary app to the CRM app and display the client double clicked in the Diary.

Regards
Rayshka
Functions.txt
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Can see why you had problems, especially with the daily lists.  For the weekly, you have at least 100 table ID's, and for the daily, over 200.   Add onto that anything else the app is doing and you can see why it's running out of table ID's

 Diary/calendar to-do's are always tough in Access because it does not have a native grid control.  Typically a grid control has one record source, which you can then filter in various ways and control the layout considerably (i.e. make it look like a calendar).  Lacking that in Access, the tendency is always to use a lot of individual controls so you can have control over the layout.

 If it were me, I would have thought more along the lines of a grid control, like a single sub form in continuous view (or a data sheet) and using color to differentiate the days/weeks, and not worried about making it look like a calendar.  That's more though about knowing Access than anything.

  What your doing is certainly not wrong by any stretch and in another product (ie. VB.NET/Winforms), your setup would not be an issue.

 I know you've done a lot of work, but the only thing that is going to help is a refactoring of the form and eliminating all those separate controls.  At that point, you could probably merge it back into the main CRM app.

<<Any suggestions would be most welcome, including how to jump from the Diary app to the CRM app and display the client double clicked in the Diary.>>

 You have two choices:

1. Two instances of Access running, with windows API code to switch back and forth between the windows (I can help with that - pretty easy to do).

2. Single instance, shut down one DB, and open the other - not very user friendly.

#1 is the better choice by far.

Let's see what Scott thinks to.

Jim.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I should add that given where your at, I think I would leave it split, setup as two apps, and use a little bit of Win API code to switch back and forth between the two MSAccess.Exe Windows.

 But as I said, let's see what Scott thinks as well.

Jim.
RayshkaAuthor Commented:
Hi Jim,
Haven't heard from Scott so I am ploughing on with the split apps approach; CRM and Diary. Could you give me direction regarding the api necessary to jump from the Diary to the CRM and open a Client (double clicked in the Diary) in the CRM please?
Best regards
Rayshka
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Rayshka,

 The code you need is in the article here:

http://www.experts-exchange.com/articles/2104/Avoiding-running-multiple-instances-of-an-application.html

 About half way down, look for the "And finally, the complete function:" and the AppAlreadyUp() procedure.

 Look for the

  ' Find the existing instance, switch to it, then close this instance

comment about half-way down in the procedure.

What your going to want to do is mark both apps by adding a Window property to each:

lngReturn = SetProp(Application.hWndAccessApp, "myApp", 1)

at start-up.

Then using the code noted above, you can switch back and forth between the two windows.

Jim.
RayshkaAuthor Commented:
Hi Jim,

Thank you for the code and your help. However, I am struggling to understand how to use this code to achieve what I want - it maybe that I have not clearly stated my issue.

It is true that I need to jump from one app to another (one-way only is required) so your code is relevant up to this point. But I cannot find a way to execute, say a function or macro when I have jumped to the target app.

I have tried code employing command line switches such as 'Call Shell("msaccess.exe " & Path & " /x " & macname, vbMaximizedFocus)' but for some reason it can't find the macro named. Nevertheless this function will always start a new instance of the app, whether one exists or not but I have included this comment incase you have a variant that does what I want.

I apologise for my persistency in this matter but this is becoming a significant stumbling block in my support of this client and I need to find a solution soonest. Notwithstanding this I fully acknowledge and thank you for your support and skills in driving this issue forward.......

Best regards
Rayshka
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Rayshka,

There are a number of ways to structure this, but here's one that should do what your need:

1. In the applications at startup, add:

lngReturn = SetProp(Application.hWndAccessApp, "MainApp1", 1)

and for the other:

lngReturn = SetProp(Application.hWndAccessApp, "MainApp2", 1)

 This "marks" the main Access window with a tag that we can find.

2. Now write a generic routine to see if the "mark" can be found:

Function AppIsLoaded(strAppMarkedAs as String) boolean

      AppIsLoaded = False

      lngHWnd = GetWindow(GetDesktopWindow(), GW_HWNDChild)

      Do While lngHWnd > 0
          If GetProp(lngHWnd, strAppMarkedAs) = 1 Then
              AppIsLoaded = True
              lngHWnd = 0
          Else
            lngHWnd = GetWindow(lngHWnd, GW_HWNDNEXT)
          End If
      Loop

End Function

3. Now write a procedure to call when you want the other app started, and switch to it:

Sub SwitchToAppMarkedAs(strAppMarkedAs as String)

   Dim oAccessApp as Object
   Dim bolFound as Booleen

   ' Find if the app is loaded, and if not, start it
   If AppIsLoaded() = False then
       ' Start the application - we can use shell or automation.  I'll use automation
      Set oAccessApp = CreateObject("Access.Application")
      oAccessApp.UserControl = True
      oAccessApp.OpenCurrentDatabase("<path to db>")

      ' Optionally run a macro or do something else here.
      ' oAccessApp.Run "<macro name>"
    End If

   ' Now switch to the app.
      ' Find the existing instance and switch to it
      bolFound = False
     
      Do While bolFound = False

        lngHWnd = GetWindow(GetDesktopWindow(), GW_HWNDChild)

        Do While lngHWnd > 0
            If GetProp(lngHWnd, strAppMarkedAs) = 1 Then
              BringWindowToTop (lngHWnd)
              lngReturn = ShowWindow(lngHWnd, 3)
              bolFound = True
              lngHWnd = 0
           Else
             lngHWnd = GetWindow(lngHWnd, GW_HWNDNEXT)
           End If
        Loop

      DoEvents

      Loop

Exit_Procedure:

    Set oAccessApp = Nothing

End Sub

That should do it for you.   You now should be able to switch back and forth between apps, and if the one your switching to is not started, it will be started.

 One note: as the app starts up, you may not want to set the window property until your startup is complete.  That way, you won't switch to the other until it's ready.

  However you may also want to do it right away so the user can see the app starting instead of sitting there waiting with nothing happening.

Jim.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and again, this is only one way to do it....you could put everything in one procedure and it would be a more efficient, but I broke it down so you had a little more flexibility.

For example, you might want to know if the other app was running without switching to it.

Jim.
RayshkaAuthor Commented:
Hi Jim,

Thanks so much for the code. Hopefully we are almost there.

I understand the code and have tailored it to my needs but I am having a problem with one of the declare statements for which I cannot find a correct statement. The declare statement is for the 'ShowWindow' in 'lngReturn=ShowWindow(lngHWnd,3)' statement. I have attached the whole module containing the code and declare statements. The procedure that causes the error is GotoClient_Click()....

The error I am getting with the module (as per the attached version) is: "Constants, fixed length strings, arrays, user defined types and Declare statements not allowed as public members of object modules" when trying to run or compile the code. This is accompanied with the ShowWindow declare statement highlighted. I have had several user defined problems associated with the ShowWindow declare statement, whilst the other declare statements appear OK as they have not produce errors!

This level of automation is new to me so bear with me please. Assuming I get the code running, I still have the problem of not knowing how to automatically run my macro (GotoPassedClient). Again your help would be invaluable.

Best regards
Rayshka
Form_subDiaryDetails.txt
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<The error I am getting with the module (as per the attached version) is: "Constants, fixed length strings, arrays, user defined types and Declare statements not allowed as public members of object modules" >>

 Needs to be in a standard module, not a form module.

Jim.
RayshkaAuthor Commented:
Hi Jim,

Sorted the declarations some needed to be in function modules whilst others, for some reason, needed to be in the class object modules.

Anyway, the code:
lngHWndDesk = GetDesktopWindow()
lngHWnd = GetWindow(lngHWndDesk, GW_CHILD)

Always returns lngHWnd=0 no matter what the second parameter is set to! GetDesktop returns lngHWndDesk=65552 but either there is an error or there are no Child windows for the Desktop window??????

Any thoughts?

Regards
Rayshka
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<or some reason, needed to be in the class object modules.>>

 None need to be in class modules.  Everything I've given you should go in a standard module (not a form or a class module).

<<Any thoughts?>>

 Are you using the 32 or 64 bit version of Access?  The code was written for 32 bit originally.  I'd have to look and see if anything needs changing for 64 bit.

Jim.
RayshkaAuthor Commented:
I'm using 32 bit Jim.

Regards
Ray
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Should all work fine then.   I've used that code for at least fifteen years now.

Jim.
RayshkaAuthor Commented:
I never for one moment doubted the code, Jim. I will keep investigating - these things are often quite obvious once it has been solved! If you should think of anything though I'd be pleased for a pointer.....

In the meantime how do we wind things up regarding the time and effort from all and in particular your most valuable help?

Regards
Ray
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Ray,

 Would it be helpful if I put a couple of small sample DB's together that demo this?

Jim.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Ray,

  Attached is a zip file with two DB's.  Unzip them to C:\temp (or change the location in the code), and set C:\temp so it's a trusted location (otherwise you'll get the "enable content").

 Each has a single form with a button that will switch to the other.

  Let me know if that helps.

Jim.
SwitchApps.zip

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RayshkaAuthor Commented:
Hi Jim,

Eureka I think.

The apps you sent helped a lot to enable me to put together a solution. I can jump to the destination app. The problem I had was that I had not set a variable - partly because I don't generally use Option Explicit; lesson learnt I guess! I now have a strong base for managing the apps separately.

The last call on your brain is to direct me as to how I run a function or macro in the destination app having jumped to it. I have tried many ways to do this with no luck....

I'm really in your debt.

Regards
Ray
RayshkaAuthor Commented:
Hi,
I guess it has been quite a while since our last correspondence on this issue. In my last comments I said that I need to be able to automatically run a macro or function after switching to an Access app:
"The last call on your brain is to direct me as to how I run a function or macro in the destination app having jumped to it. I have tried many ways to do this with no luck...."
I have never had a reply to this and the problem has again reared its ugly head so can anyone please help?

Many thanks
Ray
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Ray,

 Sorry, I somehow missed your last comment.

 Not sure I know the answer to that...I really can't think of any clean way to execute a macro or code directly.

 I suppose you could get an automation object from the window handle and call the code directly, but that would be complex.

 Doing it simply, the best I can think of would be a hidden form with a timer in each app, firing off once every xx seconds that checks a flag, and if seen, reacts accordingly.  

 But if you haven't already, post another question please.

Jim.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Rayshka,

I'd suggest you post that last as a new question.

Scott
RayshkaAuthor Commented:
OK Gents I will post a new question.....

Thanks
Ray
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.