none
How to deployment MS Access frontend/backend system when not on a company intranet RRS feed

  • Question

  • I have a functional frontend/backend access system I would like to deploy.  I've not had success using OneDrive, DropBox, ShareSync (system could crash) to store the backend to with the frontend on the users windows desktop.

    What low-cost options do I have to make this do-able?  Are there alternatives to MS Access I should consider?

    TIA,

    - Pat

    Tuesday, September 5, 2017 8:52 PM

Answers

  • If you use a VM or Remote App or Terminal Server, you have to pay for and maintain that. I do that sometimes, but only for users not allowed on some local networks who need to be separated.

    I would suggest Azure SQL Database as your backend. You can get a 2gb database for as cheap as 5 bucks a month last time I checked. Get SQL Server Management Studio to help you manage the Backend. It has no GUI creation like Access, only helps you with backend. Then go into your front end and link your tables to the Azure database/server.

    Keep the front ends on the computers/servers your users use. Avoid storing FEs on networked computers that are in another office using a VPN, not on dropbox or the like either, yuck.

    • Marked as answer by PSD1953 Tuesday, September 12, 2017 1:31 PM
    Wednesday, September 6, 2017 10:38 PM

All replies

  • Hi Pat,

    If it's a small database, you can consider using SharePoint/Office 365. Otherwise, you might consider a hosted SQL Server database, which is not cheap. There may be hosting services (like GoDaddy), which may be affordable. You don't have to stick with SQL Server. Any RDBMS like MySQL, Oracle, or PostgreSQL should work as well.

    Just my 2 cents...

    Tuesday, September 5, 2017 9:02 PM
  • To elaborate a bit on theDBGuy's answer, Access is a file server database engine. That means each client workstation directly reads and writes the backend database file. This is only suitable for use on a local network. Any remote access performs abysmally and greatly increases the risk of database corruption.

    As theDBGuy said, SQL Server or any other true database server works fine remotely. Microsoft Azure is quite inexpensive if the database size and client workload aren't too large. I would personally avoid GoDaddy because there's too much administrative overhead and nagging from them. Azure and Amazon are two reliable host networks.

    You would adjust your Access frontend to use tables linked to the remote SQL Server data storage instead of the native Access tables. If the number of rows is modest performance would be fine. If it's large enough for performance to suffer, do some reading on client-server development approaches.

    Another option that works well is using a Terminal Server, where the client workstations run a remote session on a terminal server that is local to the backend database storage.  With terminal server the actual processing happens locally and client response time is typically excellent. The user's keystrokes are forwarded to the terminal server and the screen images are returned. Since the processing is local Access works great. But it does require volume licensing and setup and administration.


    Paul

    Wednesday, September 6, 2017 9:55 PM
  • If you use a VM or Remote App or Terminal Server, you have to pay for and maintain that. I do that sometimes, but only for users not allowed on some local networks who need to be separated.

    I would suggest Azure SQL Database as your backend. You can get a 2gb database for as cheap as 5 bucks a month last time I checked. Get SQL Server Management Studio to help you manage the Backend. It has no GUI creation like Access, only helps you with backend. Then go into your front end and link your tables to the Azure database/server.

    Keep the front ends on the computers/servers your users use. Avoid storing FEs on networked computers that are in another office using a VPN, not on dropbox or the like either, yuck.

    • Marked as answer by PSD1953 Tuesday, September 12, 2017 1:31 PM
    Wednesday, September 6, 2017 10:38 PM