Access on the cloud?

[SPlatz]

Hello!

I am new to learning Access and have a question about remote use. The small business I work for is interested in developing a practice management database using Access. We have about 20 employees, many who work remotely in other states. I'm seeing some mixed information on the internet, so I'm coming to the pros. 

Our concern is to make this database easily accessible for our employees, without slow runtimes, lost data, etc.. We would love for it to be cloud-based, but have a VPN our employees currently use to access company files. I've seen some people who run the frontend on Access and host the backend on MySQL. Some seem to prefer VPN. Some say Access isn't really well-suited to remote use. 

What is the best way--is it even realistic--to use Access as a database for a mostly-remote organization? If we're going to do this we want to ensure I'm learning what will be most beneficial, whether that's access or SQL/PHP, or something else altogether. 

Thank you for your help!

[#2]

@SPlatz 

You have seen "mixed" information because there's no one right answer.

Access runs on the desktop, as you know. That imposes limitations which can't be avoided, but which can be mitigated.

Your options do include VPN or remote desktop solutions. I would avoid VPN, though. Performance with an Access interface on a remote user's computer connected over  VPN to a remote server would probably traumatize your users. Remote Desktop, in which you connect to an Access interface running inside the remote network, though, can be highly effective. It  comes with its own requirements, including the internal infrastructure needed to support it.

Other alternatives might include a cloud-based server database, such as SQL Azure, to which all of your users connect from an Access interface on their computers. 

Another one would be to consider SharePoint lists for the back end. Only very small applications are viable here because of the limitations in SP. Once you have reached a threshold of records, performance drops off badly. Theoretically, SP can handle as many as 30,000 to 50,000 records in a table, but practically, the limit is less than that. With 20 users actively and concurrently working, that's probably not a good fit either.

That then leads to considering whether Access, being a Windows desktop bound application, is a good choice. Hard to say, but it is really, really good at the kind of thing you seem to be describing, except for the remote connections. Given that, I'd probably be looking at a hybrid application, in which you have a remotely hosted server-based database, such as SQL Azure, MySQL, etc. and Access interface accdbs for the "heavy lifting" aspects of the application you need. Internal users would handle those tasks on those Access FE workstations. Remote users could have a "light" interface, browser-based, to handle the tasks they work on remotely. That's assuming, of course, you could distribute work that way.

For the browser based interface segment of that hybrid application, I'd strongly consider PowerApps. You can create pretty robust PA application that runs in the browser, connected to the same data source as your Access interfaces.