Issues with Remote Desktop Services and Microsoft Access

I have setup  RemoteDesktop Services on a Windows 2012r2 server to allow remote users to run Microsoft Access Databases.   It was working pretty good with the occasional "Your network access was interrupted.  To continue close the database, and then open it again"  error.

Lately I have been getting lots of locked files mostly Microsoft Access and one unrelated application.  

I am tied into the corporate licensing so I have enough licenses but is there a limit to the number of users on the server who can have the same application open.  (Microsoft Access being the most common issue).

I have monitoring setup and there is no network connection issue, I am not close to maxing out bandwidth, CPU or Memory.

This is quickly becoming an important issue.    

I know the recommended solution is to split the Databases so that there is a front and back end but there are to many databases and I only need to keep this going for another 3-4 weeks.   Does anyone have any suggestions
Who is Participating?
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.

Dale FyeOwner, Developing Solutions LLCCommented:
You might want to checkout this article by Luke Chung, of FMS regarding recent problems associated with Remote Desktop:
qvfpsAuthor Commented:
Thanks for the link but this is an unrelated issue.   We are able to logonto the RDS server just fine.   The issue occurs when users try to open Microsoft Access Databases.   It has been working for months but lately users have been getting the "Your network access was interrupted.  To continue close the database, and then open it again"  

The only solution I have seen for this is to redo the databases so there is a front and back end.   However due to the number of databases, the complexity of some of them and the short amount of time I need to support this that solution is not relevant.
I had a similar issue. All of our databases are front/backend. Just a few minor ones are not. We tried compacting and repair and sometimes it helped. But finally we uninstalled a recent MS update and the issue went away.

Unfortunately, that was last year some time and I can't remember what update caused it. You might try examining when things were working normal and back date any installed updates after that and see if you come across the update that may be the culprit.

Not saying that is what is doing the crashing, but in my case, that's what it was.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

John TsioumprisSoftware & Systems EngineerCommented:
Well , lets face have being working with Access just fine for some time without splitting FE/BE....and now you have issues...the solution is just to need to perform the split no matter what...even if it is for a few days ...and i reckon that theses 3-4 weeks can easily jump to to 4-5 ...5- 6 and eventually you will have an issue that remains unresolved ...probably wasting much more hours repairing and having the system unusable than taking the bullet.
A friend of mine had the same issue not so many months application "all in one".....they have being working this application for years with more than 3 people on it....and just one days issues came...the application no matter what was unusable and when more than 1 user tried to use it...kaboom...
He send it to me...splitted to FE/BE....put the FE on the users ...the BE on a server...made the linking (actually i had a small module and it was a simple server name input)...never had issues again.
qvfpsAuthor Commented:
The split is not going to happen.   We have to many databases and it would take more than the 3-4 weeks I am concerned about.   After that the server will be lightly used by only a few users.   I am just trying to keep the server going for a few more weeks as painlessly as possible.    

I had considered backing out updates but this isn't a brand new issue.  It first occurred a few months ago.  it has just gotten worse the past couple of weeks.
As a test, have you tried copying one of the databases that is having the issue locally to see if you experience the same issues? Also, if there are ODBC links, have you tried re-linking them? Sometimes it may not be the database but rather one of the link itself.
qvfpsAuthor Commented:
The issue is localized to the Remote Desktop Server.  Almost all the time I can still access the database locally while remote users on the server are having issues.    There are no ODBC drivers just a mess of linked tables.
Dale FyeOwner, Developing Solutions LLCCommented:
"the split is not going to happen".

Do I understand that this database is a single .accdb file which contains the application (forms, queries, reports, macros, and code modules) and also contains the data (tables)?  And that all of your users use this same file when logged in to their remote desktop sessions?

If so, this is likely what is causing your problems.  Any shutdown of Access that is not "normal" could corrupt this database.  This is one of the prime directives for a multi-user database:  ALWAYS split the application and the data.

I know you're adamant about not splitting the database, but this really is your best option if you wish to improve performance. If you haven't done this before, Access even includes its own built-in utility that will do it for you. (see attached pics).

I created a simple db with 1 table, 1 query and 1 form. Then ran the split db tool. Yes, I realize it is the most simplistic db, but the entire process of creating the db and its objects, then splitting them took less than 2 minutes and this is merely to make a point.

Whenever you start, you have to remember to a) make a backup of the original database and put it somewhere else on the drive, and b) make sure the end-users are pointing to the correct object, which in this case, would be the front end.

So that you don't have to go around and change everyone's desktop shortcut or in this case, the RDP shortcuts, or even if there are no shortcuts, the front end name will be the name of the original database name.

So I would rename the original database to something else, run the split, then rename the front end to the original name. The front will have all the links, queries, forms, reports and the such, and the backend will be the other database with all the data.

It should go smooth. You can then archive the backup you made before you started to some external media for safekeeping, but rename it to something like "mydatabasebeforethesplit-todaysdate.accdb". Besides being a backup of the database, it is also a backup of the underlying data.

This should reduce the size of the front-end database with the objects because there will be no data in it. The back-end is what will contain the data and only the data.


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
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
Remote Access

From novice to tech pro — start learning today.