How to connect computers at different locations using a different router/internet?
I am currently developing several macros/code using visual basic for application (VBA) using Excel to create systems such as Payroll, inventory, costing, and etc. But my issue is, organizing data becomes very tedious as the "databases" are stored within that excel workbook only and accessing that data is difficult. Also, any efforts for saving and retrieving data in "real time" becomes impossible without finding a database such as MySql to store information to. So I plan to use the MySql for Excel connector to Append data from various workbooks to the MySql database's tables.
My issue is, I do not know how to connect all the computers at the office to the database's server which can be physically very far apart from each other.
How do I do this? I was hoping the database can be accessed by anyone's computer at the office and the other branch so I can pull data out using my applications. This way the data can be updated in real time.
The only way I know to network are the simple ones such as using a Ethernet cable to connect to the other computer. Similarly, it is possible to also use Ethernet cables to connect to a network switch to allow the computers to give and retrieve information from/to each other. These are not practical as a really long Ethernet cable is needed to even consider doing this if i'd do it this way.
The most practical way I can think of is to find a way for the 2 computers to find each other through the internet. This way I can access the MySql server and make requests to it. My issue is, I do not know what the requirements and the technologies are needed to do so safely.
41 Answer
Two computers can already find each other over the Internet; that's literally what the Internet does. Although I only mention this to make a point, and in reality it is very much not recommended to expose a payroll database server (or any database server) directly to Internet connections.
In your case, if you have additional security requirements, and/or if the "server" computers are behind NAT gateways, then you use some sort of VPN (tunnel) technology to connect the two networks. There are two common options/formats:
Site-to-site VPN, where your local organization network has a single router that carries all computers' traffic over a single VPN tunnel. This needs a router that supports the correct VPN type, but it doesn't have to be the main router (however, the main router must support at least the "static routes" feature).
Client VPN (if that's the right term), where the VPN software is installed on the individual computers (each creating its own connection) and the router doesn't care about what is happening.
In both cases, the VPN "server" might be either a router, or software installed on a dedicated VPN server, or even installed on the database server itself (if you only have a single server). Sometimes cloud hosting providers have their own site-to-site VPN solutions.
2
