What is the best way to share updates to one table only?

I am developing a very small MS Access application that has four basic tables that enable users to track Customers, Assets, Crime Statistics and Vehicle Information. At first the app will only be used in one office but eventually half a dozen or so users will want to use the app. All of them will want to keep their Customer and Asset tables confidential. The Crime Stats table, though not confidential, need not be shared. However it will be to their mutual advantage to share the Vehicle Information table so that when one user updates the data the information on that vehicle will be available to all other users.

Am I wise to set up this sharing by using a VPN with all offices (separated by hundreds if not thousands of miles) reading and feeding the data from a central table, or should I set up regular automatic email updates of updated records, or some other mechanism?
Rob4077Asked:
Who is Participating?
 
lewisgCommented:
"...but is that difficult to do when working across the internet? Do I have to set up a VPN?"

If you want to have the shared information "live" it will require a VPN or Internet hosting to operate over the wide area you describe. Setting up a VPN these days is not very difficult technically but does take a bit of planning and sometimes political skills are required. Not everyone is happy with having their network connected to others.

For a VPN you will need a Internet connection at each site with a VPN capable router or VPN endpoint device. I have used Linksys RV series devices to great effect for VPNs but other devices also work well but they must all support a common protocol like IPsec. Additionally at least one site, preferably the DB hosting site, will need a static IP address. The other sites can be DHCP and use a dynamic DNS service to be locatable.

Another possible VPN route, PPTP could work if only a few clients at each location are needed. For this you would set up a PPTP server at the shared DB location and each remote workstation would configure a connection to the PPTP server. This functionality is included in all Microsoft OSs since NT. If you have a MS sever it's on there. However I would promote using a router based PPTP server like on the Linksys RV series due to security concerns.

A easier way is likely to use a shared hosting service. Even the cheapest of these usually offer mySQL or PostgreSQL database service.

Even easier (but more fraught with hazard) is to simply share a SQL server on your network. You could accomplish this via port forwarding on your router. If your Internet connection is DHCP then you will need a dynamic DNS service so the other sites can "find" the DB over the Internet. If you need this dyndns.org is the usual go to for this service.

In any case sharing a DB is a lot easier in the long run than maintaining a update process. Personally I would look at shared hosting.
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
SQL server linked table IMO.
0
 
Rob4077Author Commented:
What is IMO?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
lewisgCommented:
Since Access can draw from external MDBs it might be easier to store the shared info in an separate MDB.
0
 
telyni19Commented:
Definitely recommend splitting your database into a front end and a backend. The backend would contain just the vehicle information and be placed in a central location on a server or shared drive. The front end would contain the rest of the individual data that each user can manage as he wishes, plus a link to the backend's vehicle data. The frontend would contain all the forms and queries needed to manage all the data as well.

You could actually even split the data three ways if you plan to have a regular update cycle to the application. A local backend would contain the individual tables for the user. And a server backend would hold the shared vehicle information. The frontend would just be forms and queries and such, along with links to the tables in the other files for use by the application.
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
IMO in my opinion. Not facts.
0
 
Rob4077Author Commented:
Thanks for the suggestions.

I had planned to have 2 backends. One with the local data so the 2 or 3 users in the office could access it and the other containing the vehicle data. I like the idea of putting the vehicle data in an SQL Server database but I don't know how to hook that up so that all the 5 or 6 offices, hundreds to thousands of miles apart can access the data with reasonable response times. Is that a difficult thing to do?

I know how to create an SQL Server database and link it to my front end, when the database is on a server in the same local area network but is that difficult to do when working across the internet? Do I have to set up a VPN? Is this a project that I need to break down into dozens of steps (hence questions) I really don't know where to start so I would appreciate any guidance you can give me.
0
 
Rob4077Author Commented:
Thank you very much for the clear explanation of my possible options.

I presume that since you are suggesting a shared hosting database and most offer mySQL that MS Access will connect to a mySQL database. Is that right?

Also, you said "Even easier (but more fraught with hazard) is to simply share a SQL server on your network". I presume that the danger here is stopping unauthorised users from trying to get access to the database. I thought that SQL Serverver (even the Express - Free - edition) provided suffiient security to limit the risk. How dangerous is it?

I need to trial this before I implement it and the shared SQL Server option, if it is the easiest, would be the best way for me to set it up and test it. Of course once it is set up the temptation is to just let it be so I am trying to figure out what the risk is.
0
 
lewisgCommented:
I have not tried it but according to the folks at MySQL you can use Microsoft Access as a front-end to MySQL.

If you are going to host a MS SQL server on your network one danger is the machine being hijacked and then used as a launching point into the rest of your network. Merely disturbing your DB may be the least of your problems. If you want to go this way I would use a separate box with minimal services to host the DB.Preferably on a IP outside your internal network.

A quick Google of ms sql express hosting turned up many outfits that can do that for you. Starting at under $10 a month it could be the best option. Amazon EC2 starts at free...

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.