Ms Access replication alternative

Posted on 2008-11-12
Last Modified: 2013-11-29
I have a fairly large ms access database that we use at our main office and sevral remote offices. Currently we useremote desktop at  the remote offices to access it, but its rather slow. I have domain controllers at each site and was looking for a speedier solution. I read up about replication but have found that most say its hard to get working and even harder to maintain. I was wondering if there were any solutions like a nightly batch update from each remote office back to the main office server where the backend tables are stored. The remoteoffices are small and it would be acceptable to wait until after business hours to push the current day's data back to the main office. Any suggestions?
Question by:myndwire
    LVL 84

    Accepted Solution

    You could do something like this, but it's basically home-built replication, and you'd still have all the issues associated with replication (data conflicts, corruption, etc).

    You could move your datastore to a SQL Server, MySQL etc etc, which allows remote access, and then modify your application to use this platform. This can look to be a fairly simple move at first glance, but it can be somewhat difficult to achieve if you're not comfortable with server-based hardware/software, and simply moving your data store to a server can often result in poorer performance.

    If you want to maintain your database as-is, the simplest way to do this is through Terminal Server. If you're running SBS, then you already have Terminal Server (it's part of the Server CD), but you would need to purchase Termainal Server licenses for this. Using TS, you can host the database at one location, and remote users can fire off their TS connection and work with the database just as they do now on Remote Desktop, but with (generally) a significant increase in performance. Of course, TS comes with maintenance issues (although most TS installs, once setup correctly, are pretty low maintenance) and you'd have to either beef up an existing machine, or setup a new machine to run your TS. Note there are alternatives to TS ( is one)
    LVL 3

    Expert Comment

    SBS does not allow a terminal server installation.

    For keeping it really simple get a dedicated server with Windows 2003 Standard, join your domain, install Citrix Access Essentials and let the remote workers enjoy Citrix SSL-sessions.
    This way there is no hassle about keeping different databases in sync and the performance should be quite as good as running it locally.
    LVL 3

    Expert Comment

    You might as well try this tool:

    It should allow synchronisation of MS Access data between Access databases without utilizing the internal replication process of Access. It comes with a scheduler as well.
    LVL 84
    <SBS does not allow a terminal server installation.>

    I was under the impression that 2008 does include Terminal Server. I run 2003, along with a separate TS box on the network.
    LVL 3

    Expert Comment

    Here you go regarding terminal services in SBS 2008:

    Who would ever let users access the desktop of the main enterprise DC anyways? As the article behind the above mentioned link states, that the Premium version of SBS 2008 comes with an additional Windows 2008 standard edition which would allow the setup of terminal services.

    But having experience with both, terminal services on Win 2008 and Citrix, I would strongly recommend a Citrix Access Essentials installation in this case. It's small, easily manageble and the clients have easy access (especially via the SSL-gateway).
    LVL 84
    <Who would ever let users access the desktop of the main enterprise DC anyways?>

    I agree with that ...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    I work for a company that primarily works with small businesses as their outsourced IT vendor. As such the majority of these customers utilize some version of Small Business Server. Due to the economics of running a small business, many of these cus…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    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…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now