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?
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

PatHartmanCommented:
There are no issues with running 32 bit Office on 64 bit Windows.  That is the most common configuration since we've been running on 64 bit hardware for many years now and MS still recommends installing 32-bit Office.

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?  By the time you got around to rebuilding the BE, had the batch process completed or was it still running?  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.

Converting ADO to DAO might improve performance because that is the native method for the ACE database engine but probably not enough.
0

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
Dale FyeOwner, Developing Solutions LLCCommented:
Have you considered moving the backend to SQL Server (or the free SQL Server Express)?  I find that when the db BE gets near 1GB, overall performance takes a hit, even with good indexing.  Just a thought.

Dale
0
mlcktmguyAuthor Commented:
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.
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

PatHartmanCommented:
Simply switching the BE to SQL Server frequently makes an app slower rather than faster unless you developed the app using good client/server techniques to begin with.  For example, forms should be bound to queries and the queries should have selection criteria that limits the records returned to preferably 1 although sometimes your search criteria can only narrow down the list but all the edit forms should end up with only one record at a time.

The problem with trying to change things - such as reverting to the old server if it hasn't been scrapped is that there is only one day a month when there is a problem and so that is the day you would need to test on.  A2013 is probably slower than A2003 but not so much that this kind of problem would occur.

I think you're going to need to enlist the IT people to help track this down.  They'll need to monitor your app on a normal work day and compare that to the statement work day to see if they can identify the bottleneck.

Regarding "no changes".  A couple of years ago, my Access app stopped working sometimes but only on one form.  I finally tracked it down to when the process retrieved more than 4,000 records.  This was a historical search that just brought back a few columns but there could be quite a few rows.  I hadn't changed anything and of course "they" hadn't changed anything.  Finally after I put more filters on the form and forced the user to use them so I could bring the recordset under 4,000 always by using an arbitrary record count cut, the network guy mentioned in passing that he had installed a new firewall on the server!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!  Of course it was mis-configured and was stopping any recordset over 4,000 from being retrieved.
0
mlcktmguyAuthor Commented:
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.
0
PatHartmanCommented:
Just for giggles, ask them to make sure the firewall is correctly configured :)
0
mlcktmguyAuthor Commented:
Thanks I needed a chuckle
0
PatHartmanCommented:
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.
0
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.