Access Enters "Not Responding" State Running Reports

[rjgriffin46]

You may recall that we have deployed our small Kids Feeding Management System (KFMS) database for 4 users in our Citrix environment. Both the front ends and back end are in separate folders on the Citrix server and each user in Citrix can only open their front end copy.



Several months ago we moved from a single shared, read only front end to individual front ends for each user. See picture below of the front end folder.

This was done to address a problem where on occasion (maybe once every 1-2 months) the database would enter a Not Responding state when users were running reports. Temporary tables were used in running some of the reports and in the shared read only front end configuration I had to place them in the back end. I suspected that the updating of these temporary tables (typically via delete and append queries) by more than 1 user at a time was causing the Not Responding events. So when we switched to individual front ends, these tables were moved to the front end. Unfortunately, we are still experiencing Not Responding events at similar intervals.

I'm just beginning to look closely again at the macros and procedures that run these temporary table delete and append queries and the report record source queries to see if I've overlooked some temporary tables or what else might be causing the Not Responding events. I am considering re-creating the front end from an empty Access file if I cannot find any likely causes in case corruption is involved.

In the meantime, I'm quite interested to hear from anyone who has experienced and resolved Not Responding events while running reports or who might have some insights as to why these may be occurring. Please let me know your thoughts and if you have questions or need additional information.

Thanks as always for your assistance.

This picture includes an laccdb file opened in Notepad displaying the Citrix server as the Admin user.

[June7]

Backend is Access file? Do some tests with copy of backend on local drive. Experience same performance issue?

You could provide both files for analysis. See instructions at bottom of my post.

[Micron]

You get that message/indicator if the db engine is busy processing requests and cannot respond to further user actions until the query or whatever it is doing, has completed. Sometimes it is related to network resources, or lack of table indexing, or too many indexes or queries that are too complex and involve intrinsic functions or complex user defined functions (UDF) or remote users with slow connections or IT initiated changes that you might not know about or a combination of these and other factors. I once called IT to complain about slow queries. The response was that someone had fiddled with server settings that allocated more power to better serve themselves. When it was adjusted, my issue went away. The point is, it may not have much to do with your db so I wouldn't jump in to re-creating it in the hopes that will solve your issue.

[rjgriffin46]

I am responding to both Jun7 & Micron in this reply and appreciate your feedback:

1. The back end is also an Access file. Here is a picture of the back end folder in Citrix:
   
   
2. I have not been able to replicate this problem locally on my PC, even opening two different front ends and opening the same report on each.
3. As mentioned in my initial post, we initially deployed the database with a shared read only front end and temporary tables used for running reports in the back end. This eventually experienced the Not Responding problem so we decided to give each user their own front end with those same temporary tables. But I left a copy of those same temporary tables in the back end because we wanted to leave the shared read only front end available in case of problems with the individual front ends. I don't think this should be a problem because the links to the temporary tables in the back end were removed from the individual front ends. At this point though, since except for this problem, the individual front ends are functioning OK, I am planning to remove the shared read only front end from user's Citrix workspace and also remove the temporary tables from the back end.
4. Daryl is the primary user, virtually exclusively just running reports that lay out the menus and quantities, based on site meal counts, of items in those menus that are to go to each kids feeding site. He probably opens the reports he needs in the morning and may very well leave it open for the rest of the day. I don't know if eventually his front end becomes Not Responding or only when another user tries to run a report.
5. Before we moved to Citrix, the front and back ends were deployed on a shared file server drive. In that environment, each user's front end was on the shared drive but each user also had Access on their computer. One thing I realize about the Citrix environment is that although there is a front end for each user, they all are using the Citrix server's Access and may not even have Access on their device. I think that because of this, it might be possible that if Access encounters a problem with one user it will affect all others, as seems to be the case.
6. I think our network and server response times are generally good.
7. I will attempt to upload the front and back ends. As in the past, you may be aghast that I have not followed standard naming conventions. I think this happened because the first database I did for another purpose was a template that didn't follow standard naming conventions.

[rjgriffin46]

KFMSFE V 3.45.2 Front End 7Z.accdb.zipKids Feeding v 3.45 Back End Prod 7Z.accdb.zip

Both the zipped back end at 2455 kb and the front at 3330 kb are above the 2.00 MB limit for uploading. They are both well under the limit using 7Z format. I tried uploading the 7Z file but was rejected as an invalid file.

As an attempted work around, I 7-Zipped the files and then renamed them adding "7Z" into the name (so you'd know they were 7Z compressed) and changing the extension to "zip". After downloading, if the extension is changed back to "7z" then 7Z extraction can be used to extract to the original .accdb format. This worked for me in Windows File Explorer.

If this does not work, let me know if there is any other way to send you these files via email or drop box. It may be just too tedious, in the front end, to remove only unneeded objects.

[Micron]

I have 7zip but won't be able to look at them for most of today. If I don't find anything obvious I'll try to find a way to slim down the files further (unless someone beats me to that) or you can retry after removing objects that are not needed. Embedded pictures in command buttons and the like is one way to bloat a db. Attachments in table fields are another. You did run compact/repair before trying to zip? If you do remove stuff and intend to post that, I strongly suggest that you make sure it still has the problem over your network before doing so.

[CarlettoFed]

I would say that you have made a great confusion and a revision of the database would be necessary to regularize the structure of the Tables, with the need to pay close attention to correctly manage the Name, there must be no special spaces and signs, the various Properties of the Fields and Indices, and relative Normalization.
I enclose a small example extracted from your database, see carefully the relations window.

KidsFeedingBE.zip

[rjgriffin46]

Kids Feeding v 3.45 Back End Prod 7Z.accdb (2).zipMicron:
I'm not sure why you'd need to further slim the files if you are able to unzip them via 7Zip. I'm assuming you would download the files from the forum and then link and run them on your PC...?

One thing I forgot to do was delete the temp files from the back end. So I have done that and created a new copy of the back end using the same technique of 7Zipping and then renaming the extension to "zip". You should use this back end file to avoid duplicating the temporary files when you link the front end to the back end.

[Micron]

I'm not sure why you'd need to further slim the files if you are able to unzip them via 7Zip.

For the sake of others who might respond, because I know 7zip eliminates certain helpers here, regardless of whether or not you mask the file type. By that I mean you got around the posting problem, but they cannot unzip the file because they don't have 7zip. That being said, I didn't bother after I read the post by CarlettoFed because it seemed to me there was a problem with your design and I was waiting to see more about that from you.

Upon review of post 4 item 7, maybe you've already addressed that (except perhaps for whatever was done to the relationships). However, item 2 strongly suggests that anyone here will not be able to replicate your Citrix envnironment and cannot create a multi user environment on one pc. So before you get upset at me, note that I'm not the one who suggested you upload your files. Beyond my initial post, I was just saying maybe I could help with the format of the compression so that others could deal with it. However, I did look at it and can only get it down to around 11Mb before zipping. I'm having trouble finding out what makes it so big, even after removing your pdf attachments table.

I do note that a be file should not have queries, macros and modules in it - only tables. If your fe's are somehow linked to/using these things then that is likely a large part of your problem. Nothing should be in the be except for tables. Some fe's use local tables so that can be OK, but everything else should be in the fe.

As for the design comment, I agree - you're using real data as primary and foreign keys which is not good. In the least, you have, for example:

Items Imported.Item Type
Apple Slices Fresh Ind.Refridgerated

and in
Items.Item Type
Apple Slices Fresh Ind.Refrigerated

which is very odd given that you have used lookup fields in these tables (which really, you should not) and Refridgerated isn't even a lookup option. Also, several tables can be eliminated. One example is table Days of Week - 1 table & add a field to denote whether or not it is a delivery day.

I'm going to leave it at that because I think you're going to feel like you're being beat up here, and that is not the intent. My main point re: your original issue is that if you really have all that stuff in your be, then that is where I'd start trying to improve things. Whatever you do, do not share one fe amongst users, regardless of whether or not you're using Citrix. I think I would also do something about someone leaving a db open all day if in fact the Citrix setup means you're sharing the Access application among users, and that is something I know nothing about.

[rjgriffin46]

1. I appreciated everyone’s time and comments and I don’t wish to take up any more of your time if that can’t be productive. Here are my findings and thoughts at this point.
   
   1. Offline testing
      
      1. To further test the database as closely as possible configured like the Citrix environment, I did the following on my laptop:
         
         1. Created 3 copies of the Front End (A, B, C) and opened all reports on each with no problem. That is 10 reports and 1 query.
         2. I am leaving these open while writing this post to see if Not Responding occurs.
         3. This to some degree is like the Citrix environment in that there is one copy of Access that the 3 FEs and BE share. What is different of course is the version of Windows, networking and Citrix software between user devices and the Citrix server.
            .
      
      
   2. I apologize if not everyone has 7Zip. It is an option in Windows File Explorer on my Foodbank laptop so I assumed all Windows 10 versions had it. But I checked today and 7Zip is not an option on my personal laptop which has Windows 10 Home vs Pro on my Foodbank laptop.
      .
   3. Objects in Back End
      
      1. I apologize again for having objects there are not used and thus wasting your time evaluating them.
      2. None of the queries and macros in the BE are used in production. I think they were created while I was making table changes and could be removed.
      3. Likewise, the several Items table variants were created while initially importing menu items from a spreadsheet and one, Items Pre Refrige Correction, was a backup prior to correcting the spelling of the Refrigerated item type. Days of Week for Deliveries is also not used.
      4. The Item Type table is the lookup table for the Item Type field in the Item table. “Refridgerated” is the incorrect spelling which is what was being corrected. The Items Imported table was prior to the correction.
      5. I have used lookup fields in tables whenever appropriate. I do remember reading a developer saying they didn’t use them, but I never picked up that they shouldn’t be used. To me they are helpful to document fields that relate to a lookup table when creating or modifying table fields.
         .
      
      
   4. I believe there is only 1 attachments table linked to field and that is in the Main/Welcome form that opens when the FE is opened. There is only 1 file in this table, a pdf.
      .
   5. As far as eliminating confusion in the database tables that are used, I believe they are well normalized with no redundancy and without any inconsistent dependencies. At this time I am unwilling to undertake renaming objects.
      .
   6. Not allowing a user to keep a file open all day.
      
      1. I agree and would prefer they not keep the database open all day. But I also feel that it shouldn’t matter. Note that there is a nighly maintenance process that closes all open Citrix sessions so that at least at the start of the day no FEs are open.
         .
      
      
   7. Complexity
      The following in my mind are key and most complex parts of the database. Nonetheless, response times are very good when all is working properly.
      
      1. The query PPD Report Prod Query is the primary report query for reports on the Reports menu. Its main input table is PPD SiteMenuQty Prod and includes 9 other related tables and several expressions. “PPD” stands for Plan, Produce, Deliver and the historic table of the details of each delivery of menu items to each site.
      2. The series of queries that start with “Report 4A” all feed up to “Report 0 Final Query” are use as record source for reports on deliveries prior to PPD records being generated and also are the source for creation of PPD records.
         .
      
      
   8. As previously mentioned, we are no longer using a single, shared, read only FE. Each user has their own read/write FE located on the Citrix server. This does of course violate the standard of each user having their own copy on their PC and Access installed to be able to run it. Before we moved to Citrix, our FEs and BE were deployed on a shared network drive and each user had a copy of Access. While this Kids Feeding database was less than a year old and much simpler at the time in 2019, I don’t recall having Not Responding running reports problems in that environment.

[rjgriffin46]

After several hours while writing my 9/5/22 update, Not Responding did not occur on any of the three FE's that were open.