Deploying Microsoft Access in a Remote Environment
In 2014 I wrote the LinkedIn article: If you're looking at OFFICE 365, and you use Microsoft Access, look harder. At the time, Office 365 fell short of my expectations, mostly because of its treatment of MS Access. Initial publicity about MS Office 365 promised the ability to move MS Access databases to their cloud server. But, as my clients moved to Office 365, we discovered Microsoft promises of cloud capabilities for MS Access were inadequate. Complex Access databases could not be used from the cloud platform without major revision.
Over the years folks have asked me about using MS Access in a remote environment. In time, using a remote desktop setup surfaced as the best remote environment solution for MS Access.
Why deploying MS Access to an online Sharepoint site does not work
In theory it is possible to build and maintain MS Access applications on a Sharepoint server. However, in this case, theory is contradicted by reality. The basic reality is most MS Access databases use at least some VBA (Visual Basic for Applications) coding. Complex MS Access applications use a LOT of VBA coding. Bottom line: no online environment (including Sharepoint) supports the VBA language.
MS Access applications with VBA coding will not work in the Sharepoint environment. Since any complex MS Access application uses VBA – it is not realistic to say the application can migrate to Sharepoint. This VBA restraint is not limited to Access application. It also affects complex Excel applications using VBA script.
Microsoft tried to address this issue by driving development to MS Web Apps, which made use of Macros (instead of VBA). This push caused much frustration because macros are not nearly as versatile as writing actual VBA code. VBA is a language. Those of us who make use of it, know how to use the language. And just like English, we can use the VBA language in complex ways and create some really intricate applications. Forcing one to move from the versatility of a language to the simplicity of macros meant giving up the ability to build intuitive applications that met the complex needs of our users.
Frustrations with Microsoft’s push away from using VBA were pretty universal. Concerns ran so deep, that many folks in the business community began to wonder if Microsoft was beginning the abandonment of MS Access. If true, this reality had major implications for professionals across all sectors of the economy. MS Access is the top selling desktop database application on the market. In comparison to web side coding, Access gives professionals the ability to roll out data management applications rapidly and cost effectively. Abandonment of MS Access would mean a sharp increase in data management costs and Microsoft consumers were justifiably concerned about the matter.
In addition, professionals were also feeling irritation with Microsoft’s overall push towards a “cloud first” business strategy. Most businesses and organizations use the cloud. But, like any other tool, they prefer to use the cloud the way they want to use it and when they want to use it. They do not relish being pushed into the cloud and very often make intentional decisions to keep their data off the cloud.
In credit to Microsoft, they heard the dissatisfaction of their customers. Late last year Microsoft came out with an announcement which eased fears about abandoning MS Access and their willingness to embrace a reality where professionals drove the use of cloud, instead of Microsoft.
So… How should professionals use MS Access in the cloud?
Since Sharepoint does not support the VBA language, it is best to run MS Applications in a remote desktop environment. Remote desktop environments actually allow the desktop you see on your own computer to be run in a remote environment. If you’ve ever logged into your own desktop from home, you’ve logged into a remote desktop set up.
It is possible to setup more formal remote desktop environments for the purpose of accommodating MS Access applications. As an example, one of my clients have used MS Access for around 20 years. Over the years, their mission critical database evolved from five different MS Access database applications. The original applications used Access tables. The various databases were used for different purposes but shared similar data.
Over the years, we did the work of merging functions from the original five databases into one core database. We also did the work of migrating all data from Access tables to SQL tables. However, remote Access to their database continued to be a problem. This client needs to move their entire office to a temporary location once a year. In addition, it is not uncommon for staff members to travel. Both of these realities require remote access to data.
This client moved their mission critical Access application to a remote desktop environment. Since the migration to a formal remote desktop setup, they have been very happy with the functioning of their database. The speed is better than it was before, they can login to their database from any computer with an internet connection. It is even possible to login to their remote desktop from their smart phone.
I have tested more formal remote desktop applications myself, and been quite impressed with the capabilities. Especially the speed. Over the years I’ve logged in remotely to client’s desktops and am quite accustomed to using a remote desktop setup. But, moving that remote desktop to an off-site service is another thing altogether.
About a year ago I tested AppOnFly and was unimpressed. My connection kept dropping and when I was connected speed was an issue. The speed issue wasn’t major, but certainly it was noticeably slower than working at my own desktop. In addition the service is not based in America and I was unimpressed with customer support.
I also tested Vyon Cloud Services. Vyon Cloud services are the Quickbooks Cloud Hosting Service. The services are managed in Phoenix, AZ. The first thing that impressed me about Vyon is they are based in the United States. I was also immediately impressed with their customer service.
Testing with AppOnFly was not nearly as user friendly as testing with Vyon. For my purposes, I needed a testing environment set up with SQL Server capabilities and MS Access capabilities. AppOnFly was either not able (or willing) to set up a test environment with SQL Server capabilities.
Vyon support folks emailed me in a timely fashion and asks for my setup specs. They didn’t blink at my need for a test environment with SQL as well as Access. The test environment was operational within 2 business days. In addition, when the test environment was fully set up I received an email with a human name, a human email address and an introduction as my “technical point of contact”. This did not happen with AppOnFly (and like so many IT consumers I’ve almost stopped expecting real human contact when dealing with technical services).
As to the performance of VyonCloud, I was quite pleased. The speed was exceptional. I was able to work in SQL Server Management Studio and MS Access the same as if they were sitting on my own computer system in my own office. VyonCloud pricing is very competitive and the customer service makes it an outstanding choice in my book.
Another remote desktop option is Amazon WorkSpaces. I’ve not personally tested Amazon WorkSpaces, but many of my clients use Amazon. VyonCloud impressed me with its customer service and technical capabilities. So… I have to confess I felt no need to test further. But, the clients who do use Amazon are pleased with the service.
Another option for remote desktop capabilities are services offered by local vendors. Some of my clients use locally owned businesses for their networking needs. And those businesses offer remote desktop services.
There are no shortage of ways to migrate your MS Access application to a remote desktop environment. It becomes like any other cloud service you purchase. You will need to compare pricing, quality of customer service and technical capabilities. But, once your MS Access application is in a remote desktop environment you will be able to use it anytime you can get to a computer with internet capability
Learn More About Microsoft Access as a Data Management Tool:
- Yes - Microsoft Access Can be Used Securely
- Yes - Microsoft Access Works in a Multi-User Environment
- MS Access - Best Economical Data Management Option
- Deploying Microsoft Access in a Remote Environment
- Using Microsoft Access for Data Evolution Projects
- 1st Contact Services
