Using Access remotely online

[ontopofmalvern]

Does anyone have any experience running an Access application online.

The problem I have is I run an Access database that is central to the running of our school for children with significant emotional difficulties. All was great back in prehistory (February) when all staff and students were on site. The BE file sits on an in house server and all staff had a FE where records were added in real time throughout the day. The database has been extremely effective in managing our school – we have become very dependent on it.

Now 1/3 of staff and ½ the children are offsite receiving education remotely and the staff have no access to database other than by a very clumsiy spreadsheet which is used to insert new records very manually – it is a very nasty and incomplete solution.

I really need to be able to host the BE online – which as I understand it Access was never designed for and the solutions out there all look a bit like a square peg in a round hole.

I could do with a quick solution (couldn’t we all?). I have skimmed a few articles some of the solutions seem very technical and some costly. Does anyone have any experience with this sort of thing who could offer some pointers of what sort of thing I should be considering.

I have about 5 staff offsite at a time and it would not be impractical to restrict remote access to a single user at any one time. Whatever solution we go for data security is a major (non-negotiable) issue for us, the database contains a lot of information that is sensitive and personal. If it makes a difference to the type of soultion in terms of the SQL remote workers could be restricted to just looking at exsisting records and adding new ones (i.e. SELECT and INSERT, I could easily provide them with a front end without any UPDATE functions).

I have looked at bespoke Access web hosting and am somewhat taken back by the price, and I have considered a TeamView type solution. My instinct is to keep the BE on an in house server where most of the users are. Any pointers, suggestions wll be very much appreciated.

 

[Galaxiom]

Access doesn't like WAN connections at all. It can't handle lost packets and latency.

Windows RemoteDesktop (AKA Terminal Services) can be used to connect to the front end running on the same LAN as the backend. Windows allows one RD to run on an ordinary PC or two on a server. Otherwise you buy licences to run as many as you want on a dedicated RDS server. Another alternative is Citrix.

Don't underestimate the risk of putting a server online though. You would want to do it through a firewall and VPN.

A server backend such as Microsoft SQL Server can work with Access across a WAN but I have never done it. If I did I would be inclined to communicate with it using commands rather than linked tables. Best talk to someone with experience if you go down that track.

 

[Minty]

You can connect to an Azure-hosted backend and provided you don't move heaps of data around it works pretty well.
Move regularly used lookups to the front end, (Refreshing them on initial login), just takes slightly more careful design.

 

[ontopofmalvern]

Windows RemoteDesktop (AKA Terminal Services) can be used to connect to the front end running on the same LAN as the backend. Windows allows one RD to run on an ordinary PC or two on a server. Otherwise you buy licences to run as many as you want on a dedicated RDS server. Another alternative is Citrix.

I believe ‘Windows RemoteDesktop' is windows version of TeamView? Citrix/Azure look a less straight forward. I am drawn towards WindowsRemote/TeamView type solution, I think I can live with the user limitations and I can implement it quickly. What steps do I need to take to ensure best practice on security - what are the vulnerabilities? Does anyone have experiences comparing the two (or simailar other systems)?

Thankyou to everyone for you help.

 

[Minty]

Remote Desktop (RDP) is definitely the way to go for your environment. TeamViewer is more of a remote assistance app.
There are guides online, but the basic details are that your end users would VPN connect to your network (Very Secure), they would login to a Terminal Server RDP session preferably with their own folder and definitely their own copy of the database front end.

All they are doing is moving screen and keyboard information over the network connection, not data at all.

 

[ontopofmalvern]

I like the use of the word

definitely

I don't doubt you on this but I'm interested what benefit does the VPN give me (I presume it's a security thing)
Many thanks

 

[theDBguy]

Hi. Just throwing it out there... also have a look at Caspio or Zoho. Good luck.

 

[The_Doc_Man]

I'm interested what benefit does the VPN give me (I presume it's a security thing)

I'll step in on that security question. A VPN (Virtual Private Network) is nothing more than a way to encrypt data traveling over the internet by creating a special type of connection. We sometimes call it a virtual pipe. You shove data down the pipe. It comes out on the other end.

If you are worried about security, you have three places to consider in a remote connection. You have the central system which needs its own security. You have the remote system which needs its own security. And you have the physical connection from point A to point B. Individual system security methods includes things like anti-virus, firewall, and related software. These are "data at rest" solutions to stop folks from reaching inside your computers to grab what they want. But those only apply to the endpoints of that connection.

Data being sent over the network is "data in motion" and requires a different approach. When you establish the connection from A to B you establish a ROUTE that via some number of relaying steps will take the individual elements of your session's communication - the data packets - where they need to go. If someone wanted to "listen in" on your connection, they could use a packet sniffer or some other similar tool anywhere along the route. If they did, you would never know. But a well-implemented VPN completely guards every step of the path from A to B in one single solution. The VPN encrypts the packets before sending them so that nobody can read them until they reach the other end of the route where they will be decrypted. It isn't an Access thing but rather is a network thing.

How secure is it? The U.S. Navy employs this solution all of the time. Depending on how elaborate you want to get, it could get expensive, but an all-software-based solution IS possible. You would need this solution on EACH potential FE system AND one more on the BE server. Note that this solution applies whether you are using the "standard" Access SMB protocol or the remote desktop RDP protocol because again, it is NOT an Access solution. It is a network-layer solution. And if you are going to go with a VPN for your remote connections, it might be better to think "RDP" rather than trying to manage a more traditional FE/BE connection. Software-only VPNs can slow you down a bit, but the hardware solution is almost certainly prohibitively expensive for people working at home.

 

[ontopofmalvern]

Many thanks for all inputs, I think I am now in firmly in the RemoteDesktop with software VPN camp, I can live with the restrictions and can set it up quickly (and I understand how it works which is always a bonsus).

 

[The_Doc_Man]

Just remember that for multiple users via RDP, you need for each one to have a "private" folder/directory in which to store a separate copy of the FE file. If you open the isolated FE file via RDP, that is going to give you the best possible result given what you have described to us. The biggest pitfall with RDP-based sessions is that the host admins typically don't understand this: If you share an FE file between two users, whether across the net or via RDP, you risk corruption due to "destructive interference." (Translation: Left hand doesn't know what right hand is doing.) However, if you have COPIES of the FE separate for each user, there is no locking conflict in the FE file because each person has their own private copy. Not shared? No conflict! That simple.

Note also that you need to clarify with your network administrator that you will be doing this. It is typical (and, really, advisable) that if you have a loss of signal, an RDP session will not allow reconnection. I.e. if there is a dangling RDP because the network dropped for a second, you cannot log back in to that session. It is now dead. That "no reconnect allowed" setting is recommended by security people all over the place and MOST commercial "Internet Security" packages would call out a security flaw if this setting wasn't TRUE. Therefore, IF you have a flaky network connection, be aware that you will still face problems. However, because the Access FE would be running on the same server (or at least, local to that server) as the BE, you would not face a network loss between FE and BE. It is in that specific connection that most database corruption will occur due to drops. RDP will protect you from that case.

 

[Pat Hartman]

I've used RD in many situations to work remotely as a programmer. It works because I'm given a physical computer at the client location to log into. The physical computer at the client site is connected to their LAN. So, I connect to the computer, the computer gives me access to the LAN.

For users, I prefer Citrix but RD can also work. In both cases,you need a new server to run either Citrix or RD. The server manages multiple "desktops" and each user has a personal directory to keep his "c" drive separate from his coworker's "c" drive.

Having your own Citrix or RD server on site allows your app to be used both by local LAN users and remote workers.

If you don't have the infrastructure to run your own Citrix/RD server on site, you can use a third-party service BUT, this will restrict you to using ONLY remote connections. Since the third-party service isn't on your LAN, you can't have local LAN users.

Neither RD nor Citrix require changes to the Access app. If it works fine on your LAN now, moving it to RD or Citrix is not likely to impact it negatively.

 

[ontopofmalvern]

okay I think I have to go on for now, the only problem I have with our network admin is that I run it alongside about 2,000 other jobs - at most for the foreseeable there will be no more than 5 remote users requiring a few minutes a day. Inputs from remote users will not be time critical, any time that day will do. So I'll keep it simple one shared user and if they have to wait there is always a kettle. Thank you very much I really appreciate the time contributors put into this forum along with the ability to copy and paste you have built my database.