Syncronizing MS Access databse file

Question

I am developing a database with about 10 tables in it. Basically it will be used in 2 or 3 distant geographical locations (let's call them A,B and C). The desired work flow will be as follows: A,B and C should always have the same database. So when A does any changes he should be able to send those changes over to B and C. Emailing the entire mdb file doesnt make sense since its 15+mb in size. So I would like to send the new additional records and changes only to B and C. The changes B and C make should also be reflected to the other repective parties. How can I do this? I have a few ideas in mind but cont know how to implement it.

solution 'A' - export the data tables only into a xls file and email that. But the importing of the tables into the mdb file could be a bit complex right? and the xls is file will also become bigger and bigger with time.

solution 'B' - try extract just the changes and email only the new parts? (but how to extract just those)

Solution 'C' - find some way of syncing all users onto the same database(storage) location. I was thinking of a front/back end splitting solution by storing the tables in a shared drive in the parent company's server (which is also overseas). But the network connection between locations is very slow, and I dont know how much bandwidth is needed for this.

Any recomendations would be most welcome!

Answer 1

Appriciate any help you guys can give!! thanks!!

Answer 2

You want to use "Jet Replication". See

• MSDN Search for jro at http://social.msdn.microsoft.com/Search/en-US?query=jro&ac=8

• MSDN Search for access replication at http://social.msdn.microsoft.com/Search/en-US?query=access%20replication&ac=3

Answer 3

You're heading in the right direction with your Solution C ... all users share the same data store. However, you should not attempt to share an Access database file across a wide area network connection. Doing that guarantees you will corrupt the database; it's only a matter of time.

Consider using SQL Server as your data store. Your Access application could then become a front end for data stored in SQL Server. If you don't want SQL Server, there's plenty of other choices. I've used PostGreSQL. I see others using MySQL. Basically, I think you can use any database server which supports ODBC connections (I don't know of any which don't).

SharePoint is another possibility. You can create lists in SharePoint and link to them in your Access application as "virtual" tables.

In any case I'm trying to steer you away from Solutions A and B. Those smell like pointy-haired boss solutions to me. "We'll just give the users copies of the database and let them share their changes with emails. Easy peasy, right? That's why I get paid the big bucks!" Gack!

It's not easy when you consider the details. We email new records from one location to the others. But how do we handle updates to existing records? What if users in different locations make incompatible changes to the same record? Can anyone in any location ever delete a record? If so, how do we transmit that information to everyone else?

I urge you to avoid all that by using a single data store which all the users can share.

Edit: You can safely use an Access database file as the shared data store if you choose one of the three locations to house the database file, and allow users from the other 2 locations to connect with Terminal Server or Citrix. In that case, the remote users' connections to the database would be local to the server, and you would minimize the risk of corruption.

Answer 4

It's been some time since I did it, but the indirect method of replication worked well for me in a similar situation.

It takes something to set up. The documentation used to be appalling for it, but I found articles written by Michael Kaplan (aka Michka) that walked me through how to do it.

If your final environment is going to be fairly stable, then use Access the whole way. If not, then I'd urge you to take HansUp's advice and go with SQL Server or SharePoint.

Do note: if you're working in Access 2007 or later, replication is not directly supported, and you'll have to roll-your-own bits and pieces. If you're using an earlier installation, you'll be fine, but allow time for some head-scratching.

Answer 5

In regard to sources for information on replication, start with my Jet Replication Wiki.

But I would never recommend Jet replication for your scenario. The only environment where I currently recommend it (and I've been doing replicated apps since 1997 and still have several in production use) is for supporting laptop users who have to work with live data in the field disconnected from any network, and return to the home office and synch direct with the mother ship.

The easiest solutions with an Access application would be hosting the app on Windows Terminal Server/Citrix and the users would run it over a Remote Desktop Connection, or using Sharepoint. The Terminal Server/Citrix solution has no accomodation for disconnected users, but Sharepoint can accomodate offline usage and synch changes when connected. Access 2010 and Sharepoint 2010 provide a host of new features, including better schema design, the equivalent of triggers and greatly improved peformance for large Sharepoint lists, so it's a no-brainer to me that if you choose Sharepoint you'd want to use A2010 and Sharepoint 2010.

While it's possible to do what you want with Jet Replication, it requires a lot of setup on the server and client ends, and is relatively fragile (not in terms of data integrity if you're using indirect replication (as you should), but in terms of network reliability) -- there are too many moving parts and too many failure points.

Windows Terminal Server/Citrix is by far the simplest, with the fewest moving parts and completely centralized administration, and works very well for a relatively small investment.

Sharepoint is more complicated than WTS/Citrix, but is less complex and more centralized than a Jet Replication solution.

If it were me, I'd probably go with WTS/Citrix if there was no need for disconnected usage, but I'd be salivating over trying out A2010/Sharepoint 2010. If there was a need for disconnected usage, then I'd definitely go the Sharepoint route.