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?
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?
Aaron Tomosky
SQL server linked table IMO.
ASKER
What is IMO?
Since Access can draw from external MDBs it might be easier to store the shared info in an separate MDB.
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.
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.
IMO in my opinion. Not facts.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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...
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...