[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Options for synchronizing an Access Database

Posted on 2007-08-05
Medium Priority
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

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 800 total points
ID: 19634733
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 19

Assisted Solution

by:Richard Daneke
Richard Daneke earned 400 total points
ID: 19635132
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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 800 total points
ID: 19635220
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
ID: 19635262
"if you're not fluent in security, you would be well advised to hire someone "

And I would recommend:  LSM :-)


Author Comment

ID: 19635795
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

Threat Trends for MSPs to Watch

See the findings.
Despite its humble beginnings, phishing has come a long way since those first crudely constructed emails. Today, phishing sites can appear and disappear in the length of a coffee break, and it takes more than a little know-how to keep your clients secure.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing emails are a popular malware delivery vehicle for attack.  While there are many ways for an attacker to increase the chances of success for their phishing emails, one of the most effective methods involves spoofing the message to appear to …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

872 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