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

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.

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

"Please advise if I need to do anything else...." - did you do as I suggested? Your feedback didn't tell me.

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

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)

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

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

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?

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

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

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

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

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.

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

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.

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

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

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.

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

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

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

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

I'm using 32 bit Jim.

Regards
Ray

Should all work fine then.   I've used that code for at least fifteen years now.

Jim.

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

Ray,

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

Jim.

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

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

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.

Rayshka,

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

Scott

OK Gents I will post a new question.....

Thanks
Ray