Options for synchronizing an Access Database

Posted on 2007-08-05
Last Modified: 2013-11-05
I am planning to develop an Access 2000 Database to store patient data for my small medical practice.  Three or four people might be users of the database.  One, maybe two, will be laptop users working outside the office at hospitals or other patient care sites.  At the office I have Small Business Server 2000 and 4 Windows XP client desktops.

A partial view of the table relationships would be:

                     ContactID <<---------------  ContactID (GUID)
                     Problem                              PatientID <<----------------PatientID
                     Exam                                  Date                                 PatientName
                     Assessment                      Time                                 Date of Birth
                     etc                                      UserID                              Etc.

So, let's say I have the backend of the database on the server sitting at "x."
I go to the hosptial and enter contact "x+1", so does my medical assistant back at the office.
So, now there are two "x+1" contacts.

I know that this might be an example where replication might be used or some might recommend a Terminal Server approach.  Alternatively should I generate some GUID based on code which I have seen on this site which takes the date/time date of the record creation and randomizes it somehow.

With the Terminal Server approach, I believe I would have to buy new hardware and software.  Plus I need to make sure the server connection is HIPAA compliant.

I would appreciate some advice.

Question by:rreiss60
    LVL 75

    Accepted Solution

    This can be done with Access Replication.  I have two clients who do this daily with 3-4 laptops ... for a couple of years.  But ... make a quick copy and paste backup of the mdb prior to replicating ...

    Tools>>Replication>>Create Replica ... and go from there.  It's very cool for this sort of thing.  

    This should get you started:

    There are several articles at this link from the Master of Replication ... Michael Kaplan:

    LVL 18

    Assisted Solution

    by:Richard Daneke
    Your SBS server will permit two Terminal Server connections without additional licensing.  Documentation mentions that this will be Administrator services, but this will permit your laptops to connect and authenticate with user name and password.
    With the small office size of your network, they could log on remotely and use Access on the server to work.  This will eliminate the hassles of replication and you could use Autonumber for your  ContactID.  Access will assign unique autonumber to anyone adding a patient - even if everyone starts a new contact at the 'same time'
    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    In this case, replication would probably be a better choice ... TS requires an internet connection, which may not always be available. With replication, your laptop users would always be able to work, even with no internet connection. Replicated databases use a numbering scheme which pretty much eliminates duplicates, but depending on the way your db is setup you could, possibly, run into duplicates. If you allow Access to handle the numbering (i.e. use AutoNumbers) then you should have no problem.

    Be VERY careful when deploying an Access application if you must be HIPAA compliant ... if you're not fluent in security, you would be well advised to hire someone who can make sure your app is locked down correctly. It's pretty straight forward to break in to an Access database with minimal time, effort, and money ... just something to be aware of.
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    "if you're not fluent in security, you would be well advised to hire someone "

    And I would recommend:  LSM :-)


    Author Comment

    Two of three experts recommend replication.  I like the idea of being able to work even if the internet an internet connection isn't available for some reason.  Also

    As far as security goes, I am planning to have full disk encryption for the computers as well as password protections.

    I will increase the points and distribute them accordingly.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    By default, Carbonite Server Backup manages your encryption key for you using Advanced Encryption Standard (AES) 128-bit encryption. If you choose to manage your private encryption key, your backups will be encrypted using AES 256-bit encryption.
    I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    731 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

    15 Experts available now in Live!

    Get 1:1 Help Now