2

I have an Access database on my server. We split it up and use the front-end database for search data and adding new records or reports in local computer. If we update or add a new record, that writes to the back-end of database. I want to use this database in the other building with other servers. Also, those servers have no direct connection. How can I sync both back-end databases to keep the database data up to date?

These details may be useful: It's a big amount of data - about 25,750 client records. I guess there are more than 25 tables at 80 MB.

fixer1234
  • 28,064
user327472
  • 23

1 Answers1

0

This is not an easy task with an Access DB, I think you really have 3 options:

  • Connect the 2 LAN with a VPN (over the internet), and use a remote desktop over the VPN from LAN2 to access the DB frontend installed in a PC in LAN1 (that will use the DB backend using the local LAN1)
  • Connect the 2 LAN with a VPN (over the internet) and share the same backend DB over the VPN
  • Create a mid-layer server, shared over the internet, or over a VPN, that make the Access MDB accessible by different front-end on different lan

Using a remote computer with a remote desktop is the easiest, and have few risks... but you need a spare computer on the network which is holding the DB.

Sharing an MDB Access DB over a VPN is doable, but it's risky, because you loose the VPN connection when the frontend is writing to the backend, the backend can became corrupted (is you choose this way, please do daily backup... or even hourly backup) VPN are slow, so writing to an Access DB over a vpn is slow, and this increase the chances that when the VPN go down (and this will happen) you are writing data to the DB, and so are risking a DB corruption.

Creating a mid-layer server, that share the DB access over a VPN/internet is a very safe option... but it's also the more complex to implement.

Also, maybe this is more a question better suited for StackOverflow than SuperUser... maybe some admin can migrate the question?

Max
  • 1,099