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