Link to home
Start Free TrialLog in
Avatar of mlcktmguy
mlcktmguyFlag for United States of America

asked on

SSlow Processing and 'System Resources Exceed' In Access 2013 on 2012R2 server

Short Version:
Over the course of 2017 we upgraded any of our clients that were on Access 2003 to Access 2013.  We have only had an issue with only one client and on only one day of the month.

Within the last few months we upgraded a client's Access 2003 application to Access 2013.  The 2003 application had been in production for over 3 years prior to the upgraded.  It is a split application with a separate front end and back end's.  The backend’s are MS Access also.  Due to the size of the clent’s files, there are two backend DB'sThere, each of about 1GB in size.

Most of the 2013 changes were to the menu's, using ribbons and the new popup menu's.  The applications logic for the most part unchanged.  We tried to taking the 'if it ain't broke don't fix it' approach as much as possible.

Since the upgrade to 2013, for the most part there have been no issues.  The exception being one day at the beginning of every month when client statements are being generated.  The users have been reporting extremely slow processing with some getting the ‘System Resources Exceeded’ Error.

Are there any know issues running 32 bit Access 2013 on a 64 bit operating system that would result in slow processing?

More detail:
Every morning at 5:00am a compress/compact is run on both backend data bases.  This has been the procedure since the application was brought up for the client over three years ago.

On the November statement day, the users reported extremely slow processing and some users were getting the 'System Resources Exceeded' message.  Our users have been trained to exit the application and get a fresh copy of the accde if they encounter errors.  They were all taking those steps and I verified that the daily compact/repair had been run successfully at 5:00am.  The slowness persisted and I got the call from IT.  I suggested that all users exit so IT could run the compact repair again.  That was done and there was no improvement.  I finally took the extreme step of creating two blank backend accdb’s and importing all of the tables from the two existing DB’s into the new DB’s.  After I made this change the application speed was much improved and there were no further complaint.

I say creating blank DB’s is an extreme step because in my over 15 years of developing and installing access applications, I have only had to resort to that process one other time, at another client.  The cause at the other client was an extremely slow network and was resolved with hardware/network changes.  No program revisions were made and the problem never re-occurred.
Keep in mind the statement generation process is not new and prior to the upgrade to 2013 there were never any issues.
After the November occurrence I just thought we had encountered some odd condition that caused the issue.  I didn’t even connect the fact that the problem occurred on statement day.   I only made the connection when the exact same thing happened on statement day in December which could only be resolved by completely re-importing the data tables into new accdb’s.  So there is definitely as issue that needs resolution.

I have looked thru the code and really can’t find much, if any, room for improvement. Over the last six months, some before the 2013 conversion and some after, I did revise code on most of the retrieval/read only data retrievals from:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

to

Set db = getCurrentDbC
'
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset(selectString, dbOpenForwardOnly, dbReadOnly)  ''''///////  Read Only ///////

I thought it was a no brainer that using DAO dbOpenforwardonly, dbreadonly would be faster than ADO adopenKeyset, adLockOptomistic.

Am I wrong?

Another moving part in the upgrade, which I had no control over,  was that IT set up a new server for the 2013 version of the application.  The new Server has 2012R2 which to me looks a lot like Windows 8/10.  The Old Server was 2008R2 version which was similar to In 7.  This seems to me like it could have had an impact on the application but IT says no and I really don’t know that world at all, so I can’t question that?

That’s why I’m posting here.  I am already thankful if you are interested enough to read this far.  I know this is a small book but I wanted to explain everything as thoroughly as possible.

Do any of the three moving parts (access 2013, converting ADO to DAO readonly or the 2012R2 OS) send a red flag as being a performance issue?
Any recomendations or things to check that might improve performance?
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mlcktmguy

ASKER

Pat:
Could it simply be a coincidence that in November, your network reached it's limit and now Access (which is a heavy user of network resources) is over the edge?
It wans't a one time issue. The exact same issue re-occurred on December statement print day.
I suggested network as a possibility but the tech people that monitor the load and memory usage found them to be very low.  They specifically said it is not an overloaded network issue.  Since 'Statement Day' has been in the workflow for over three years without issue in Access 2003 on the 2008 server I though maybe it was due to some kind of interaction between Access 2013 and the new 2012 server.

By the time you got around to rebuilding the BE, had the batch process completed or was it still running?
It's not a single batch process that causes the issue just a lot of requests for customer statements.

I find the fact that the issue arises only on this one day very compelling.  Is it possible to change the timing of the statement process?  Did those people implement a new version that might have caused the network overload?  I would at least take a look at that.

I agree with you 100%.  On the client side there is no new version nor has there been any workflow changes that would increase activity at this time.  I don't know if workflow revisions can be made.  My frustration in all of this, is that it worked fine for three years and now it doesn't.  The moving parts that I am aware of between when it was OK to now it's not are:
1, Upgrade to Access 2013
2. Running on new server with 2012R2 instead of 2008R2
3. I changed some of the read only retrieval routine from ADO to DAO readonly as shown above.


Dale:  The backend DB's have not grown significantly over the last 3, 6  or 12 months.  After compression both backend DB's are under 1GB but one will grow to just over 1GB throughout the day.  
SQL Server is definitely the long term plan but there is no time or resources for that option at this time.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Pat, thanks so much for your comments.  I have the same thoughts as you.  The issue may be somewhere in the details of the server change/and or the OS change.  

I told IT it was a big mistake to change more than the necessary moving parts to bring up the 2013 application but they insisted on the new server and new OS.  When this issue occurred I asked them to do research on there side and reminded them that almost all of the issues that came up in testing the new 2013 app prior prior to production were related to the new server or OS.  They just quote memory usage and cpu load, network load as all being very low.

I have had similar experiences to your situation with the server, in the past with this client.  Issues I have raised have magically disapeared with no acknowledgement from IT that they changed anything.  It was better on the old server when I was 100% positive no application changes had been made.  Unfortunately, in this case the application has changed and is in a new version of Access.

The IT people are employees and I am an outside consultant.  The client users have no knowledge of what IT does or what I do, so they are in no position to evaluate.  That's why I am trying to investigate every possibility on the application side and possibly even uncover issues that may be on the Server/network/OS side.

I will ask them to monitor the usage on days other than 'statement' day so that we can establish a baseline.  

If you have any other ideas please let me know.  I appreciate your interest in this situation.
Just for giggles, ask them to make sure the firewall is correctly configured :)
Thanks I needed a chuckle
I love chuckles, especially the green ones :)  I is always a problem talking to the IT folks.  They think because you work with Access that you know nothing.  Bring them chuckles.