Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-05-13
9
Medium Priority
?
240 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:Rob4077
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 35753574
SQL server linked table IMO.
0
 

Author Comment

by:Rob4077
ID: 35753623
What is IMO?
0
 
LVL 7

Expert Comment

by:lewisg
ID: 35753989
Since Access can draw from external MDBs it might be easier to store the shared info in an separate MDB.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:telyni19
ID: 35756611
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
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 35757718
IMO in my opinion. Not facts.
0
 

Author Comment

by:Rob4077
ID: 35759716
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
 
LVL 7

Accepted Solution

by:
lewisg earned 2000 total points
ID: 35760373
"...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
 

Author Comment

by:Rob4077
ID: 35762224
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
 
LVL 7

Expert Comment

by:lewisg
ID: 35762403
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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question