Passing data over the WAN RRS feed

  • Question

  • Hallo Everyone,

    We have an MS-Access application used by several people "in the field". Currently they export CSV files and we load these into our central database.  But this is rather "clunky".

    What would be ideal would be to pass that data over the WAN but of course there are many issues of speed and of security. ODBC is probably not the right tool(?)

    The central (master) database is also MS-Access but we are considering changing this to a server-database (MS-SQL, Oracle, MySQL or Postgre). The communication between the distributed locations and the central DB would be minimal with these probably being simple passthrough SELECTs and INSERTs.

    Does anyone have a suggestion for an appropriate architecture for this set-up?

    Any tips would be most gratefully received.



    PS: We tried using MS-Access "replication" but got into a terrible state with that: We have quite a few one-to-many referential relationships set up and I think the replication got stuck on all these.

    Friday, October 21, 2016 6:05 AM

All replies

  • What version of Access are you using? Replication in Access has been discontinued I believe since Access 2007.

    Your best bet would be to move the master database to SQL Server and then use SQL Server replication. This is a major undertaking, but it is possible to set up replication over slow WAN connections.

    Matthias Kläy, Kläy Computing AG

    Friday, October 21, 2016 10:27 AM
  • As you know a multi user database is split into 2 files; everyone has a copy of the front on their local PC and they all link to the same common back end file.

    In general attempting to put a WAN between the front and back does not work.

    The terminal services approach, which would be the same using a remote connection technology (GoToMyPC.com) is to keep those front files on premise.  Each remote user must still have their own front file, they cannot attempt to share one, but they remote into their front.  The front/back is on the same LAN and so this works fine and the issue really comes down to the lag experience of the remote connection experience.

    If your users have a robust continuous internet, then beginning with Access 2013 one can split the database where the back file/tables are SharePoint lists on an Office 365 Account in the cloud.  This works fairly well though noticeable think time and it is not clear how many records could cause issues and overall documentation is fragmented.

    The final choice of course is to move to a web app.

    Friday, October 21, 2016 7:58 PM
  • If you are always going to be connected then the suggestions that msdnPublicIdentity made are good.  It may even be workable with SQL Server, Oracle, etc. to put a WAN between your Access front ends "in the field" and your back end database depending on your particular application.  I would not try this with an Access back end under any circumstances though.
    If you cannot rely on consistent internet connection in the field then the suggestion that Matthias made is a possibility. SQL replication would allow you to synchronize your remote and central databases but may be an overly complex solution for your case.
    Another possibility is to move your central database to SQL Server or similar and maintain your remote databases in Access. It sounds like you do not necessarily need a full-blown replication solution in that it appears your data only needs to move in one direction.  In that case you would only need to write some code for your remote users to run to update the central database during those times when they can be connected.  Again, I would not try this with an Access back end, but with a SQL Server back end the amount of traffic over the WAN should be greatly reduced.


    Friday, October 21, 2016 10:21 PM