Sharing Access over WAN

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

I want several users who don't have Access on their machines to update the same backend tables. Should I use SQL as the backend, or is there a better solution?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
What exactly are the metrics of your WAN?  Describe it in detail.
(and see my comment in your other Q that just closed)

mx
You don't speak much of how the updates are triggered - is it an application you control, web pages, or something else? This may affect whether you decide to use ODBC or another connectivity technology (ADO/DAO).

Sticking with Access is okay, as long as you setup your locking correctly, and you don't expect amazing performance. This is possible to do, without installing Access on any of the computers, as the Jet driver has been part of the standard distribution of data drivers for some time.

You can quickly get into the database by setting up an ODBC data source which uses the Jet engine, link it to your shared database file(s) on the network, and go!
Database Architect / Application Developer
Top Expert 2007
Commented:
"and you don't expect amazing performance."
In fact, under the right scenario, you can expect amazing performance. I see it daily where I work.

mx
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
We haven't set up the database on the user's machines yet. They don't have Access. I think that we will have to use the Access runtime
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Is it truely a WAN or is it a LAN.

You will need to deploy the Access Runtime (free) with your application when you put it on your users computers.  If you truly have a WAN, I would recommend SQL Server Express (also free) as your backend, but if you have a LAN, you can use Access as the backend as well as the front-end.
@DatabaseMX:

It is certain that for some tasks, Access is fantastic. But it is very easy to get into some odd situations where the performance on tables with large numbers of rows or columns falls off a cliff.

I have also had customers that decided to use an Access back-end for a web application. In their "dev" environment, it was working fine. Then, as soon as they had more than 10 users hitting it live, it would suffer horrible performance issues.

Therefore, I was trying to err on the side of caution, rather than promising the moon...

As with everything in IT - one must understand their environment.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
In general you should not share an Access database across a WAN. If you can insure optimal conditions, stability and network speeds, as mx seems to enjoy, then you could certainly make this work. In the real world, however, most WANs are done over standard internet hops, which are far too unreliable and unstable to work for Access.

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thank you all for the input

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial