 |
Data Taming for Small Businesses |
| How to Move an Access Database to the Web (for Remote Access) | |||||||||||||||||||||
 PDF version |
|||||||||||||||||||||
You have an Access database program you’ve been using for your business or organization for several years.
Perhaps it was created by a developer or person with a lot of knowledge about Access.
Now you want to make the database remotely available. Perhaps you have staff that needs to work from home.
Perhaps you want to be able to use it on a tablet, or even a smartphone.
So how do you make it available remotely or on the web?
There are several options for accomplishing this. The best option depends on your unique situation.
Perhaps you’ve tried the idea of people using Remote Desktop for connecting to their office computers.
That means their office computers always have to be turned on. But maybe you’ve run into the issue of
having more staff working remotely than there are office computers available.
You may have considered converting the database into a web program, but aren’t sure how to accomplish it.
In order to understand all your options you should first understand a fundamental point about Access
databases - and databases in general.
The fundamental point is that an Access database consists of two major components: a front end and a
back end.
The front end is the part that you see and interact with. These are forms, reports, and queries.
The back end is the data that the forms, reports, and queries present to you.
The normal configuration is to have one copy of the front end for each user. All the front ends connect to one shared back end. In a typical office there’s a file sharing computer that has the back end. Each user is running a copy of the front end. The front end pulls data from the back end to display in its forms, reports, and queries. It saves data updates into the back end. Here is a summary chart of the pro’s and con’s for various options, with discussion below:
Remote Desktop - Cloud Desktops 
With this option users connect to cloud desktops with Remote Desktop. Each cloud desktop has a copy of the Access front end. All the front ends are connected to one shared back end, which is in a file sharing location in the cloud. People can use their home computers and laptops from anywhere. Office computers also connect to the cloud database. This option is not good for small mobile devices like smartphones. The Access database program looks and acts the same as it did before on regular desktops. So it takes up the same screen area and requires keyboard and mouse inputs. Normally this option is the quickest to implement. It’s a matter of signing up with a cloud desktop provider and uploading the files without making any changes. It’s also the least expensive for setting up. You have to be careful to shop for a provider with a reliable track record for uptime, support, and a monthly or yearly pricing service schedule that fits your budget. You have no control over uptime and service outages. Remote Desktop - on-premise Remote Desktop Services 
With this option users connect to desktops with Remote Desktop. The desktops are virtual desktops managed by a server computer at your business office. Unlike using a cloud desktop service, you have complete control over the system, and don’t have to pay monthly or yearly service fees. One downside to this option is that you need a qualified person to set up the server. This would be a Windows server running Remote Desktop Services (Terminal Services in earlier versions of Windows Server). If you don’t already have a usable server you’ll have to invest in the purchase of one with the power to handle the workload. You also have the cost of purchasing enough licenses for remote users. Another challenge is that you need to have a reliable office internet connection so the database will be available to remote users when they need it. Internal office workers can still use the database as before. They wouldn’t need an internet connection because everything is in your office network, not out in the cloud. VPN connection to on-premise file server 
With this option all remote users will need a copy of Microsoft Access installed on their home computers or laptops, just as will all internal office computers. The back end is stored on a file sharing computer in the business office. Remote workers don’t use Remote Desktop. Instead, they log into a VPN (virtual private network). This connection makes a home computer part of the office network. The home computer acts like any other computer that’s part of the network at the office location. This option requires a very fast internet connection at the business location, and a very fast server computer. Otherwise the performance of the database application will be unacceptably slow. This option can be quick to implement if you already have a very fast internet connection and server computer, and it has the advantage of not paying a monthly or yearly fee for a cloud hosting service. You have complete control over it. Back end on cloud file system storage 
This option uses a cloud storage drive to store the back end. All office and remote computers must have Microsoft Access installed on them. All of them share the back end on the cloud storage device. Don’t be tempted to use a file sharing service like DropBox for your Access back end. You should use a service that uses the SMB (server message block) method for accessing files. An example is Microsoft’s Azure File Storage. Back end converted to cloud database, front end remains in Access
This option uses a back end in the cloud that is not an Access database file. Instead, the back end is a fast-performance database server such as SQL Server or SQL Azure. You’ll want to consider this option if you have concerns about how big your database is, and degradation in performance as it grows. By converting the back end to a cloud database back end you can guarantee fast performance. The downside to this option is that it will require making some programming changes to the front end to take advantage of a database server back end. Otherwise performance could actually be much worse. The more parts there are to the Access application, the more time will need to be spent by a programmer making adjustments to the way data is retrieved and saved. Front end and back end converted to cloud application
The advantage to this option is that you can have a high performance application that’s available to all computers and many mobile devices. The downside is that it will require a developer to recreate the front end from scratch. The more complex the application, the more up-front time and cost required to get it up and running. Be wary of claims about services like Zoho and Quickbase. These services are promoted as giving you the ability to quickly upload an Access database to the cloud. The only problem is that it’s the back end that can be uploaded – not the front end too. The front end will need to be recreated from scratch. Also, it might be difficult or impossible to replicate some of the same functionality as the desktop Access application. The same problem holds true for the web version of Access. Even though it’s a Microsoft service, it doesn’t offer an easy way to move a complex application to the web. The other issue is that it requires your business to use Office 365. |