Getting Microsoft Access to Work Remotely

[Ebo_Quansah]

Today's post was written by Juan Soto, a Microsoft Access Developer and MVP.

Ever wanted to use your Access database remotely? Great news, Microsoft Access can be used remotely under certain conditions, and here are the best ways to do so:

1. Remote Desktop: The easiest and fastest way to remotely consume your database is through a remote desktop software; from Microsoft’s Remote desktop to other commercial software that will allow you to take over your work PC remotely and continue using the database as if you were in the office (See Get Microsoft Remote Desktop and How to Use Remote Desktop)
2. If you haven’t already, upgrade your database to SQL Server in order to leverage the power of SQL with Access; you can find more info on it HERE: please note you may need to create SQL Server views and stored procedures based on the complexity of your Access database, and although occasionally timely, this is certainly a worthwhile effort.
3. VPN: If your database is composed of both Microsoft Access Front End and a SQL Server backend, than make sure you are running the Front End file on your local PC after you connect via VPN to work, otherwise it may be slower and unreliable.

About the Author:

Juan Soto is the President of IT Impact and a leading professional in the industry. He has been named an Access MVP by Microsoft since 2011 and is a frequent author on the official Microsoft Access blog as well as the co-founder of AccessUserGroups.org, where groups of Access enthusiasts around the world meet once a month on a wide range of topics. You can reach Juan at https://accessexperts.com/contact/

[Transimpex]

Hi Ebo,

we also have very good experiences with Access 2010 Runtime/SQL Server based RemoteApps on Windows Server RDSH. Relatively inexpensive License Costs, excellent Performance, Stability, Scalability, Security, Ease of FrontEnd-Deployment/Updates and last but not least macOS and even Android support are strong Plus Points here.

Since we will soon have to set up a new Terminal Server, the question arises whether Office 365 Access Runtime is officially supported or recommended on Windows Server 2019 RDSH. Is it as robust as MSI Versions used to be in this Scenario? Are there any Pitfalls in Installation?

[Gustav Brock]

@Transimpex - a couple of tips:

• Create the Windows Server 2019 RDSH as a virtual machine hosted by Windows Server 2019. This makes any kind of disaster recovery or farming much simpler
• Let the RDSH VM run off an SSD formatted with ReFS
• You can use the Access Runtime, but if the users are licensed to Office 365 (Microsoft 365), this can be installed without consuming one of the user's five licenses. The Office Deployment Tool is a must to install Office 365 (Microsoft 365) on an RDSH machine
• Rename your frontend.accdb to frontend.accdr to make it launch in runtime mode.

[sarah_poore]

Thanks for this information

[MattyBrown]

@Transimpex, following on from what @Gustav Brock posted,

• When licensing Microsoft Office 365 for use with Remote Desktop or RemoteApp, your users need to be licensed for a subscription that includes Shared Computer Activation, such as Microsoft 365 Business Premium.  See https://docs.microsoft.com/en-us/deployoffice/overview-shared-computer-activation.  You must also install Office 365 or the Access Runtime using ODT and configure your XML configuration file to use Shared Computer Activation.  On top of Office 365 costs, you also need Windows RDS User or Device CALs and Windows User or Device CALs suitable for 2019 or whatever version you're installing (though you likely already have the Windows CALs).
• To prevent users from changing the VBA code in your database file, save it out to an accde file so that the VBA code is compiled and the source code is removed... then as suggested by @Gustav Brock, rename that file to accdr to ensure that if the file is opened by the full version of Access rather than the runtime, the database will run as if it was opened by the runtime.  This doesn't prevent users from seeing your local and linked tables or changing the contents of them directly, but it does prevent a malicious actor from adding code that damages your systems, removing password checks, taking a look at your hard-coded passwords or password tables, stealing your source code, etc.

[ln21amb]

I have MSAccess 2019 running with FE and BE in a LAN environment. I  would like to run the FE part (GUI) on Browser. Do you know any tool/plugin that is able to run MS ACCESS on a Browser (for instance a plugin on WordPress or DotNetNuke), obviuously it need to connect on the BE Access DB (but I can also link all in a unique ms access file). So what it is important is the possibility to reuse the MS Access application in a Internet Portal, accessible via browser. Any solution for HW is allowed (Hosting on internet provider, cloud, stand-alone server conected to internet,....)

Thanks

[Transimpex]

@ln21amb 

Windows Server 2019 introduced a new feature in the Remote Destop Services Section called WebClient. This is a browser based alterntaive to classic native Remote Desktop Clients for Windows, macOS, Android etc. It works very well and the cool thing is, that an Access Applicaton can really have the look and feel of a Browser Application. No need to install a specific RDP-Client or to lauch a specific RDP-File. All what the User needs is an ordinary brower and an URL.

Of course, it is not entirely uncomplicated to set up such a Windows Server and the license costs are not cheap either. Office license costs can at least be saved when using the Access 365 Runtime.

But if you have taken on this effort once, then this is a great and scalable platform for the easy distribution and use of Access applications!