Best way to share Access Database over a network (FE/BE configuration)

[Silvia]

Hello,



I realize there are tons of articles on the Internet addressing this issue and that's precisely my problem: too many articles talking about stuff I don't know about for me to be able to ascertain which solution would be best for me. So that's why I'm here asking for a few tips and recommendations to point me in the right direction so I can focus my research a bit.

I will describe the issue. First of all, let me say that while I'm a reasonably experienced programmer (both in MS Access and not), but I've always programmed Desktop software so I've no idea about web services, web servers or anything like that, so please break it down a bit for me, OK? =)

Now, I have 2 situations.

In one case, I have a FE/BE configuration for a small family business set up in Spain. They have set up the BE in a shared folder in the main computer of the office and several computers have access to this shared folder and, therefore, the BE. Then, each computer has a copy of the FE on their Desktops and everyone works at the same time against the same BE. It has been working very well so far. Problem: some of the employees are going to start working remotely so we need to set up either the BE or the whole program (I'm not sure) somewhere so it can be accessed over the Internet - they work in an area where Internet reception is really good so there should be no problem with that.

Now, my first thought went to this SharePoint thing that you can see in several points in the Access Program, however, my FE has lots of VBA programming and from what I've been able to find, SharePoint doesn't support that, so there goes that option (and no, the sort of VBA code that I have doesn't translate into macros). So what other options do I have? I've read about moving my BE to a database server product like SQL Server - which I would do gladly as I'm used to working on that and the MS Access SQL limitations drive me up the wall - however I still do not see how that would help me access the database over the Internet. Oh, and since this is a small business, if there is any solution that can be done for free, that would be best =P


Now, my other case. Again FE/BE configuration for a small business, but this time it's set up in a tiny island in Indonesia so Internet reception is absolutely unreliable. In this case, up to now, they've been using the program on a single computer, however, they now want to be able to access it through more than one computer. A shared folder over the wifi is not an option and they do not want to set up cables or whatever is needed to set up an intranet because there are no computer technicians to set it up and they are not going to do it. So. I thought they could buy one of those external hard drives that create their own wifi so that the computer connects to the hard drive. This way the wifi comes from this device and it will be stable, strong and reliable. I am guessing they can probable make this hard drive into a network drive or a shared folder or something and use it as a shared server of sorts? Would this work?


I know I'm asking a lot, but I will be very grateful for any help you might provide. Like I said at the beginning, you do not need to give me the full solution, if you can just point me in the direction of something that fits my needs I will do my own research. I'm just a bit overwhelmed with all the possibilities and all these technologies that I don't know about...

Thanks a lot!

Silvia.

[RuralGuy]

Have you considered Citrix as a possible solution?

[Silvia]

I hadn't heard about it, so thanks for that. I'll take a look!

[rpeare]

For situation 1, they may be able to set up a VPN.

For example, they would have a front end on their remote computer. The database, because it's a front end, would not run or be able to do anything without the VPN connection being up. Once the connection is up the remote computer could map a network drive from the host location (you can set this up so the mapped drive will refresh when the VPN is active as well) where your back end is stored. Then opening the front end would work just as if the back end was local to you, it just might run a bit slower.

For situation 2, I would be hesitant to offer a solution when they don't appear to be willing to do any setup and/or simply do not have access to the resources to fix something if it goes wrong, adding pieces to this may cause more problems than they have now.

[CJ_London]

there is very little you can do across the web which is free, there will be a subscription or license of some sort to pay.

If you are looking at Citrix, also look at terminal server. In both cases both front and backend are located on the server. Each user has their own user profile so each user still has their own front end in their user area on the server. One of the advantages of this route is speed - the only thing being transferred across the network is screen repaints one way and key press and mouse events the other. From the users perspective it is almost as if the back end is located on their local drive.

Another alternative is Microsoft Azure - basically a web based SQL Server. Front end on your users personal machine. But queries, forms, reports etc must be optimised to minimise network traffic.

SharePoint has its limitations as a database but still has its uses. You can still index tables but so far as I am aware there are no facilities on the table side for managing relationships, data validation, etc. There is a 5k limit for number of records returned (for performance reasons) - but I do believe you can still link to those tables and continue to use a VBA based front end.

Not sure how these two statements gel

A shared folder over the wifi is not an option
...
...
they could buy one of those external hard drives that create their own wifi

Why can't they have a shared folder on one of the machines and use a wireless router?

Either way, Access and wireless connections is not a good mixture. Although it can be stable, it can still fail for a few microseconds resulting in potential corruption of the back end. If going this route, consider using sql server express as a back end.

In addition you need to consider performance. It is difficult to be precise because it depends on the quality of equipment and cabling, but transfer speed comparisons between a local back end (the current single user option) and using a wired LAN are approx 6 to 1 - Local will run at say 6GB/s, wired Lan 1Gb/s. Wireless now has some impressive speeds, up to circa 0.8GB/s, perhaps more for the latest technology, but the wireless drives you are talking about have much slower wireless performance - typically I'm seeing reported circa 0.05GB/s (about the same as the old dial up modems). So your network performance will drop to around 1% of the current single machine option used by your clients. Minimising network traffic with efficient query/form/report design will help, but I doubt enough to make the system workable.